package Swbclear;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class g_Sqlutils {

    private static Logger logger = Logger.getLogger(g_tables.class);


    /**
     * 创建 天猫，京东，苏宁，美团，饿了么，美团团购，大众点评修改好region_id的表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem5
     * @param name5
     * @param timeStamp
     */
    public static void o2o_allPlat_shop_BDL_tm_jd_create(Connection conn,
                                                         String schem1, String name1,
                                                         String schem2, String name2,
                                                         String schem5, String name5,
                                                         String timeStamp

    ) {
        Statement stmt = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            stmt = conn.createStatement();
            //执行普通SQL语句。
            int rc = stmt.executeUpdate(
                    /**
                     *1.修改region_id
                     */
                    "create view \"" + schem1 + "\".\"" + name1 + "\" as\n" +
                            "select\n" +
                            "distinct t1.*,\n" +
                            "t2.uniqueid reg_id\n" +
                            "from\n" +
                            "(select \n" +
                            "*,\n" +
                            "case \n" +
                            "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then town\n" +
                            "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市' or city = '东营市') then aedzid\n" +
                            "else district end  districts\n" +
                            "from \"" + schem2 + "\".\"" + name2 + "\") t1\n" +
                            "left join\n" +
                            "(select distinct * from dbadmin.\"address_mapping_p_c_d_show_2018\" where timeStamp = " + timeStamp + ") t2\n" +
                            "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district);" +
                            /**
                             * 3.修改region_id为null
                             */
                            "create view \"" + schem5 + "\".\"" + name5 + "\" as\n" +
                            "select distinct t1.*,t2.uniqueid region_id from \n" +
                            "(\n" +
                            "select shopid,province,\n" +
                            "case when (\n" +
                            "province like '北京市' or province like '上海市' \n" +
                            "or province like '重庆市' or province like '天津市' ) then province else city end as city,\n" +
                            "cast('0' as text) as districts,totalsellcount,totalsalesamount,timestamp,platformid  \n" +
                            "from \"" + schem1 + "\".\"" + name1 + "\" where reg_id is null ) t1 \n" +
                            "left join \n" +
                            "(select distinct * from dbadmin.\"address_mapping_p_c_d_show_2018\" where timeStamp = " + timeStamp + ") t2\n" +
                            "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district);"

            );


            stmt.close();
        } catch (SQLException e) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入 京东到店铺基本表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem3
     * @param name3
     */
    public static void o2o_allPlat_shop_BDL_tm_jd_insert(Connection conn,
                                                         String schem1, String name1,
                                                         String schem3, String name3
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "insert into \"" + schem3 + "\".\"" + name3 + "\"\n" +
                            "(region_id,shopid,sellcount,salesamount,timestamp,platformid,userId,shopName)\n" +
                            "select distinct Reg_ID,shopid,totalsellCount,totalsalesAmount,timeStamp,platformid,'-1' as userId,shopName\n" +
                            "from \"" + schem1 + "\".\"" + name1 + "\" where province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛') and is_oversea != 'true';");

            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入 天猫到店铺基本表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem3
     * @param name3
     */
    public static void o2o_allPlat_shop_BDL_tm_insert(Connection conn,
                                                      String schem1, String name1,
                                                      String schem3, String name3
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "insert into \"" + schem3 + "\".\"" + name3 + "\"\n" +
                            "(region_id,shopid,sellcount,salesamount,timestamp,platformid,userId,shopName)\n" +
                            "select distinct Reg_ID,shopid,totalsellCount,totalsalesAmount,timeStamp,platformid,userId,shopName\n" +
                            "from \"" + schem1 + "\".\"" + name1 + "\" where province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛') and is_oversea != 'true';");

            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 创建 淘宝，拼多多修改好region_id的表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem5
     * @param name5
     * @param timeStamp
     */
    public static void o2o_allPlat_shop_BDL_taobao_pinduoduo_create(Connection conn,
                                                                    String schem1, String name1,
                                                                    String schem2, String name2,
                                                                    String schem5, String name5,
                                                                    String timeStamp

    ) {
        Statement stmt = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            stmt = conn.createStatement();
            //执行普通SQL语句。
            int rc = stmt.executeUpdate(
                    /**
                     *1.修改region_id
                     */
                    "create view \"" + schem1 + "\".\"" + name1 + "\" as\n" +
                            "select\n" +
                            "distinct t1.*,\n" +
                            "t2.uniqueid reg_id\n" +
                            "from\n" +
                            "(select \n" +
                            "*,\n" +
                            "case \n" +
                            "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n" +
                            "when (city = '苏州市'  or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n" +
                            "else district end  districts\n" +
                            "from \"" + schem2 + "\".\"" + name2 + "\") t1\n" +
                            "left join\n" +
                            "(select distinct * from dbadmin.\"address_mapping_p_c_d_show_2018\" where timeStamp = " + timeStamp + ") t2\n" +
                            "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district);" +
                            /**
                             * 3.修改region_id为null
                             */
                            "create view \"" + schem5 + "\".\"" + name5 + "\" as\n" +
                            "select distinct t1.*,t2.uniqueid region_id from \n" +
                            "(\n" +
                            "select shopid,province,\n" +
                            "case when (\n" +
                            "province like '北京市' or province like '上海市' \n" +
                            "or province like '重庆市' or province like '天津市' ) then province else city end as city,\n" +
                            "cast('0' as text) as districts,totalsellcount,totalsalesamount,timestamp,platformid  \n" +
                            "from \"" + schem1 + "\".\"" + name1 + "\" where reg_id is null ) t1 \n" +
                            "left join \n" +
                            "(select distinct * from dbadmin.\"address_mapping_p_c_d_show_2018\" where timeStamp = " + timeStamp + ") t2\n" +
                            "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district);"

            );


            stmt.close();
        } catch (SQLException e) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入 拼多多到店铺基本表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem3
     * @param name3
     */
    public static void o2o_allPlat_shop_BDL_taobao_pinduoduo_insert(Connection conn,
                                                                    String schem1, String name1,
                                                                    String schem3, String name3
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "insert into \"" + schem3 + "\".\"" + name3 + "\"\n" +
                            "(region_id,shopid,sellcount,salesamount,timestamp,platformid,userId,shopName)\n" +
                            "select distinct Reg_ID,shopid,totalsellCount,totalsalesAmount,timeStamp,platformid,'-1' as userId,shopName\n" +
                            "from \"" + schem1 + "\".\"" + name1 + "\" where province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛') --and length(shopid)=32;");

            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 公司信息表
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     */
    public static void o2o_allPlat_shop_BDL_company_insert(Connection conn,
                                                           String schem1, String name1,
                                                           String schem2, String name2
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "insert into \""+schem1+"\".\""+name1+"\"\n" +
                            "(timestamp,platformid,shopid,company_name,address,region_id)\n" +
                            "select distinct timestamp,platformid,shopid,name,address," +
                            "CASE  WHEN substr(regional_id,5,6)='00' and platformid='9' THEN substr(regional_id,0,4) else regional_id END region_id" +
                            " from \n" +
                            "\""+schem2+"\".\""+name2+"\"");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 更新淘宝个人企业表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem3
     * @param name3
     */
    public static void o2o_allPlat_shop_BDL_companytaobao_insert(Connection conn,
                                                                 String schem1, String name1,
                                                                 String schem2, String name2,
                                                                 String schem3, String name3,
                                                                 String timeStamp
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "--备份数据\n" +
                            "delete from " + schem1 + ".\"" + name1 + "\";\n" +
                            "insert into " + schem1 + ".\"" + name1 + "\"(\n" +
                            "shopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "\tcompany_or_personal\n" +
                            ")\n" +
                            "select\n" +
                            "shopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "\tcompany_or_personal\n" +
                            "\tfrom " + schem2 + ".\"" + name2 + "\"   where timestamp='" + timeStamp + "' ;\n" +
                            "--删除数据\n" +
                            "delete from " + schem2 + ".\"" + name2 + "\" where timestamp='" + timeStamp + "';\n" +
                            "\n" +
                            "\n" +
                            "insert into " + schem2 + ".\"" + name2 + "\"(\n" +
                            "shopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "\tcompany_or_personal\n" +
                            "    )\n" +
                            "    select \n" +
                            "\tt1.shopid,\n" +
                            "\tt1.sellcount ,\n" +
                            "    t1.salesamount  ,\n" +
                            "    t1.\"timestamp\" ,\n" +
                            "    t1.platformid ,\n" +
                            "    t1.start_time ,\n" +
                            "    t1.end_time ,\n" +
                            "    t1.region_id ,\n" +
                            "    t1.is_liveshop ,\n" +
                            "    t1.userid ,\n" +
                            "    t1.shopname,\n" +
                            "    case\n" +
                            "\twhen t1.platformid in ('9') and length(t2.company_name) in (6,9,12) \n" +
                            "\tand t2.company_name not in ('-1','0','1','01','测试','测试店铺','金徽商贸','金合商贸','蓝天商贸','金恒商贸','博宇商贸') \n" +
                            "\tand substr(t2.company_name,1,1) not in ('0','1','2','3','4','5','6','7','8','9') then '2'\n" +
                            "\twhen t1.platformid in ('9') and length(t2.company_name)>12 \n" +
                            "\tand t2.company_name not in ('-1','0','1','01','测试','测试店铺') \n" +
                            "\tand substr(t2.company_name,1,1) not in ('0','1','2','3','4','5','6','7','8','9') then '1'\n" +
                            "else '2'\n" +
                            "end as company_or_personal\n" +
                            "from " + schem1 + ".\"" + name1 + "\" t1\n" +
                            "left join\n" +
                            "(select * from " + schem3 + ".\"" + name3 + "\" where platformid='9' and timestamp='" + timeStamp + "') t2\n" +
                            "on t1.shopid=t2.shopid and t1.platformid=t2.platformid\n" +
                            "where t1.platformid='9' and t1.timestamp='" + timeStamp + "'"
            );
            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 更新苏宁个人企业表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param timeStamp
     */
    public static void o2o_allPlat_shop_BDL_companysuning_insert(Connection conn,
                                                                 String schem1, String name1,
                                                                 String schem2, String name2,
                                                                 String timeStamp
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "--备份数据\n" +
                            "insert into " + schem1 + ".\"" + name1 + "\"(\n" +
                            "shopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "\tcompany_or_personal\n" +
                            "    )\n" +
                            "    select \n" +
                            "\tshopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "    '1'\n" +
                            "from " + schem2 + ".\"" + name2 + "\" where platformid in ('3') and timestamp='" + timeStamp + "'");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 更新天猫个人企业表
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param timeStamp
     */
    public static void o2o_allPlat_shop_BDL_companytmall_insert(Connection conn,
                                                                String schem1, String name1,
                                                                String schem2, String name2,
                                                                String timeStamp
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "--备份数据\n" +
                            "insert into " + schem1 + ".\"" + name1 + "\"(\n" +
                            "shopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "\tcompany_or_personal\n" +
                            "    )\n" +
                            "    select \n" +
                            "\tshopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "    '1'\n" +
                            "from " + schem2 + ".\"" + name2 + "\" where platformid in ('10') and timestamp='" + timeStamp + "'");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }



    /**
     * 更新其他平台个人企业表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param timeStamp
     */
    public static void o2o_allPlat_shop_BDL_companyqita_insert(Connection conn,
                                                               String schem1, String name1,
                                                               String schem2, String name2,
                                                               String timeStamp
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "--备份数据\n" +
                            "insert into " + schem1 + ".\"" + name1 + "\"(\n" +
                            "shopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "\tcompany_or_personal\n" +
                            "    )\n" +
                            "    select \n" +
                            "\tshopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "    '0'\n" +
                            "from " + schem2 + ".\"" + name2 + "\" where platformid  in ('16') and timestamp='" + timeStamp + "'");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 更新其他平台个人企业表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param timeStamp
     */
    public static void o2o_allPlat_shop_BDL_companyqitaelm_insert(Connection conn,
                                                                  String schem1, String name1,
                                                                  String schem2, String name2,
                                                                  String timeStamp
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "--备份数据\n" +
//                            "delete from " + schem2 + ".\"" + name2 + "\";\n" +
//                            "insert into " + schem2 + ".\"" + name2 + "\"(\n" +
//                            "shopid ,\n" +
//                            "    sellcount ,\n" +
//                            "    salesamount  ,\n" +
//                            "    \"timestamp\" ,\n" +
//                            "    platformid ,\n" +
//                            "    start_time ,\n" +
//                            "    end_time ,\n" +
//                            "    region_id ,\n" +
//                            "    is_liveshop ,\n" +
//                            "    userid ,\n" +
//                            "    shopname,\n" +
//                            "\tcompany_or_personal\n" +
//                            ")\n" +
//                            "select\n" +
//                            "shopid ,\n" +
//                            "    sellcount ,\n" +
//                            "    salesamount  ,\n" +
//                            "    \"timestamp\" ,\n" +
//                            "    platformid ,\n" +
//                            "    start_time ,\n" +
//                            "    end_time ,\n" +
//                            "    region_id ,\n" +
//                            "    is_liveshop ,\n" +
//                            "    userid ,\n" +
//                            "    shopname,\n" +
//                            "\tcompany_or_personal\n" +
//                            "\tfrom " + schem1 + ".\"" + name1 + "\"\n" +
//                            "where timestamp='" + timeStamp + "' and platformid in ('13');\n" +
//                            "--删除数据\n" +
//                            "delete from " + schem1 + ".\"" + name1 + "\" where timestamp='" + timeStamp + "' and platformid in ('13');\n" +
                    "--备份数据\n" +
                            "insert into " + schem1 + ".\"" + name1 + "\"(\n" +
                            "shopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "\tcompany_or_personal\n" +
                            "    )\n" +
                            "    select \n" +
                            "\tshopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "    '0'\n" +
                            "from " + schem2 + ".\"" + name2 + "\" where platformid  in ('13') and timestamp='" + timeStamp + "'");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }



    /**
     * 更新其他平台个人企业表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param timeStamp
     */
    public static void o2o_allPlat_shop_BDL_companyqitamttg_insert(Connection conn,
                                                                   String schem1, String name1,
                                                                   String schem2, String name2,
                                                                   String timeStamp
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "--备份数据\n" +
                            "insert into " + schem1 + ".\"" + name1 + "\"(\n" +
                            "shopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "\tcompany_or_personal\n" +
                            "    )\n" +
                            "    select \n" +
                            "\tshopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "    '0'\n" +
                            "from " + schem2 + ".\"" + name2 + "\" where platformid  in ('24') and timestamp='" + timeStamp + "'");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 更新其他平台个人企业表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param timeStamp
     */
    public static void o2o_allPlat_shop_BDL_companyqitadzdp_insert(Connection conn,
                                                                   String schem1, String name1,
                                                                   String schem2, String name2,
                                                                   String timeStamp
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(//补充删除语句---临时使用
                    "--备份数据\n" +
//                            "delete from " + schem2 + ".\"" + name2 + "\" ;\n" +
//                            "insert into " + schem2 + ".\"" + name2 + "\"(\n" +
//                            "shopid ,\n" +
//                            "    sellcount ,\n" +
//                            "    salesamount  ,\n" +
//                            "    \"timestamp\" ,\n" +
//                            "    platformid ,\n" +
//                            "    start_time ,\n" +
//                            "    end_time ,\n" +
//                            "    region_id ,\n" +
//                            "    is_liveshop ,\n" +
//                            "    userid ,\n" +
//                            "    shopname,\n" +
//                            "\tcompany_or_personal\n" +
//                            ")\n" +
//                            "select\n" +
//                            "shopid ,\n" +
//                            "    sellcount ,\n" +
//                            "    salesamount  ,\n" +
//                            "    \"timestamp\" ,\n" +
//                            "    platformid ,\n" +
//                            "    start_time ,\n" +
//                            "    end_time ,\n" +
//                            "    region_id ,\n" +
//                            "    is_liveshop ,\n" +
//                            "    userid ,\n" +
//                            "    shopname,\n" +
//                            "\tcompany_or_personal\n" +
//                            "\tfrom " + schem1 + ".\"" + name1 + "\";\n" +
//                            "delete from " + schem1 + ".\"" + name1 + "\" where timestamp='" + timeStamp + "';\n" +
                            "\n" +
                            "insert into " + schem1 + ".\"" + name1 + "\"(\n" +
                            "shopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "\tcompany_or_personal\n" +
                            "    )\n" +
                            "    select \n" +
                            "\tshopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "    '0'\n" +
                            "from " + schem2 + ".\"" + name2 + "\" where platformid  in ('23') and timestamp='" + timeStamp + "'");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 更新其他平台个人企业表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param timeStamp
     */
    public static void o2o_allPlat_shop_BDL_companyqitahmxs_insert(Connection conn,
                                                                   String schem1, String name1,
                                                                   String schem2, String name2,
                                                                   String timeStamp
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "--备份数据\n" +
//                            "delete from " + schem2 + ".\"" + name2 + "\";\n" +
//                            "insert into " + schem2 + ".\"" + name2 + "\"(\n" +
//                            "shopid ,\n" +
//                            "    sellcount ,\n" +
//                            "    salesamount  ,\n" +
//                            "    \"timestamp\" ,\n" +
//                            "    platformid ,\n" +
//                            "    start_time ,\n" +
//                            "    end_time ,\n" +
//                            "    region_id ,\n" +
//                            "    is_liveshop ,\n" +
//                            "    userid ,\n" +
//                            "    shopname,\n" +
//                            "\tcompany_or_personal\n" +
//                            ")\n" +
//                            "select\n" +
//                            "shopid ,\n" +
//                            "    sellcount ,\n" +
//                            "    salesamount  ,\n" +
//                            "    \"timestamp\" ,\n" +
//                            "    platformid ,\n" +
//                            "    start_time ,\n" +
//                            "    end_time ,\n" +
//                            "    region_id ,\n" +
//                            "    is_liveshop ,\n" +
//                            "    userid ,\n" +
//                            "    shopname,\n" +
//                            "\tcompany_or_personal\n" +
//                            "\tfrom " + schem1 + ".\"" + name1 + "\"\n" +
//                            "where timestamp='" + timeStamp + "' and platformid not in ('9','10','5','3','20','16','13','23','24');\n" +
//                            "--删除数据\n" +
//                            "delete from " + schem1 + ".\"" + name1 + "\" where timestamp='" + timeStamp + "' and platformid not in ('9','10','5','3','20','16','13','23','24');\n" +
                    "--备份数据\n" +
                            "insert into " + schem1 + ".\"" + name1 + "\"(\n" +
                            "shopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "\tcompany_or_personal\n" +
                            "    )\n" +
                            "    select \n" +
                            "\tshopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "    '0'\n" +
                            "from " + schem2 + ".\"" + name2 + "\" where platformid not in ('9','10','5','3','20','16','13','23','24') and timestamp='" + timeStamp + "'");
            //
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 更新京东个人企业表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param timeStamp
     */
    public static void o2o_allPlat_shop_BDL_companyjd_insert(Connection conn,
                                                             String schem1, String name1,
                                                             String schem2, String name2,
                                                             String schem3, String name3,
                                                             String timeStamp
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "insert into " + schem1 + ".\"" + name1 + "\"(\n" +
                            "shopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "\tcompany_or_personal\n" +
                            "    )\n" +
                            "    select \n" +
                            "\tt1.shopid,\n" +
                            "\tt1.sellcount ,\n" +
                            "    t1.salesamount  ,\n" +
                            "    t1.\"timestamp\" ,\n" +
                            "    t1.platformid ,\n" +
                            "    t1.start_time ,\n" +
                            "    t1.end_time ,\n" +
                            "    t1.region_id ,\n" +
                            "    t1.is_liveshop ,\n" +
                            "    t1.userid ,\n" +
                            "    t1.shopname,\n" +
                            "    case\n" +
                            "\twhen t1.platformid in ('5') and length(t2.company_name) in (6,9,12) \n" +
                            "\tand t2.company_name not in ('-1','0','1','01','测试','测试店铺','金徽商贸','金合商贸','蓝天商贸','金恒商贸','博宇商贸') \n" +
                            "\tand substr(t2.company_name,1,1) not in ('0','1','2','3','4','5','6','7','8','9') then '2'\n" +
                            "\twhen t1.platformid in ('5') and length(t2.company_name)>12 \n" +
                            "\tand t2.company_name not in ('-1','0','1','01','测试','测试店铺') \n" +
                            "\tand substr(t2.company_name,1,1) not in ('0','1','2','3','4','5','6','7','8','9') then '1'\n" +
                            "else '1'\n" +
                            "end as company_or_personal\n" +
                            "from " + schem2 + ".\"" + name2 + "\" t1\n" +
                            "left join\n" +
                            "(select * from "+schem3+".\""+name3+"\" where platformid='5' and timestamp='"+timeStamp+"') t2\n" +
                            "on t1.shopid=t2.shopid and t1.platformid=t2.platformid\n" +
                            "where t1.platformid='5' and t1.timestamp='"+timeStamp+"'");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 更新拼多多个人企业表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param timeStamp
     */
    public static void o2o_allPlat_shop_BDL_companypinduoduo_insert(Connection conn,
                                                                    String schem1, String name1,
                                                                    String schem2, String name2,
                                                                    String schem3, String name3,
                                                                    String timeStamp
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "insert into "+schem1+".\""+name1+"\"(\n" +
                            "shopid ,\n" +
                            "    sellcount ,\n" +
                            "    salesamount  ,\n" +
                            "    \"timestamp\" ,\n" +
                            "    platformid ,\n" +
                            "    start_time ,\n" +
                            "    end_time ,\n" +
                            "    region_id ,\n" +
                            "    is_liveshop ,\n" +
                            "    userid ,\n" +
                            "    shopname,\n" +
                            "\tcompany_or_personal\n" +
                            "    )\n" +
                            "    select \n" +
                            "\tt1.shopid,\n" +
                            "\tt1.sellcount ,\n" +
                            "    t1.salesamount  ,\n" +
                            "    t1.\"timestamp\" ,\n" +
                            "    t1.platformid ,\n" +
                            "    t1.start_time ,\n" +
                            "    t1.end_time ,\n" +
                            "    t1.region_id ,\n" +
                            "    t1.is_liveshop ,\n" +
                            "    t1.userid ,\n" +
                            "    t1.shopname,\n" +
                            "    case\n" +
                            "\twhen t1.platformid in ('20') and length(t2.company_name) in (6,9,12) \n" +
                            "\tand t2.company_name not in ('-1','0','1','01','测试','测试店铺','金徽商贸','金合商贸','蓝天商贸','金恒商贸','博宇商贸') \n" +
                            "\tand substr(t2.company_name,1,1) not in ('0','1','2','3','4','5','6','7','8','9') then '2'\n" +
                            "\twhen t1.platformid in ('20') and length(t2.company_name)>12 \n" +
                            "\tand t2.company_name not in ('-1','0','1','01','测试','测试店铺') \n" +
                            "\tand substr(t2.company_name,1,1) not in ('0','1','2','3','4','5','6','7','8','9') then '1'\n" +
                            "else '2'\n" +
                            "end as company_or_personal\n" +
                            "from "+schem2+".\""+name2+"\" t1\n" +
                            "left join\n" +
                            "(select * from "+schem3+".\""+name3+"\" where platformid='20' and timestamp='"+timeStamp+"') t2\n" +
                            "on t1.shopid=t2.shopid and t1.platformid=t2.platformid\n" +
                            "where t1.platformid='20' and t1.timestamp='"+timeStamp+"'");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }





    /**
     * 插入 淘宝到店铺基本表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem3
     * @param name3
     */
    public static void o2o_allPlat_shop_BDL_taobao_insert(Connection conn,
                                                          String schem1, String name1,
                                                          String schem3, String name3
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "insert into \"" + schem3 + "\".\"" + name3 + "\"\n" +
                            "(region_id,shopid,sellcount,salesamount,timestamp,platformid,userId,shopName)\n" +
//                            "(region_id,shopid,sellcount,salesamount,timestamp,platformid,userId,shopName)\n" +
                            "select distinct Reg_ID,shopid,totalsellCount,totalsalesAmount,timeStamp,platformid,userId,shopName\n" +
                            "from \"" + schem1 + "\".\"" + name1 + "\" where province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛');");

            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入 盒马到店铺基本表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem3
     * @param name3
     */
    public static void o2o_allPlat_shop_BDL_hmxs_insert(Connection conn,
                                                        String schem1, String name1,
                                                        String schem3, String name3
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "insert into \"" + schem3 + "\".\"" + name3 + "\"\n" +
                            "(region_id,shopid,sellcount,salesamount,timestamp,platformid,userId,shopName)\n" +
                            "select distinct Reg_ID,shopid,totalsellCount,totalsalesAmount,timeStamp,platformid,'-1' as userId,shopName\n" +
                            "from \"" + schem1 + "\".\"" + name1 + "\" where province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛');");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 更新 店铺基本表region_id为null的数据
     *
     * @param conn
     * @param schem3
     * @param name3
     * @param schem5
     * @param name5
     * @param timeStamp
     * @param platformid
     */
    public static void o2o_allPlat_shop_BDL_update(Connection conn,
                                                   String schem3, String name3,
                                                   String schem5, String name5,
                                                   String timeStamp, String platformid
    ) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(
                    "update \"" + schem3 + "\".\"" + name3 + "\" t1\n" +
                            "set t1.region_id = t2.region_id\n" +
                            "from\n" +
                            "\"" + schem5 + "\".\"" + name5 + "\" t2\n" +
                            "where t1.timeStamp = '" + timeStamp + "' and t1.platformId = t2.platformId and t1.shopId = t2.shopId");
//                            "where t1.timeStamp = '" + timeStamp + "' and t1.platformId = '" + platformid + "' and t1.shopId = t2.shopId");
            int i = pstmt.executeUpdate();
            logger.info("[Number of data update:" + i + "]");
            pstmt.close();
        } catch (SQLException e) {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入 天猫，京东畅销店铺表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param platformid
     * @param timestamp
     */
    public static void o2o_bestsell_shop_APP_jd_tm_insert(Connection conn,
                                                          String schem1, String name1,
                                                          String schem2, String name2,
                                                          String schem3, String name3,
                                                          String platformid, String timestamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address)\n" +
                    "SELECT\n" +
                    "distinct region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "region_id,t1.shopid,sellcount,salesamount,timestamp,platformid,shopName,name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address, \n" +
                    "row_number() over(partition by region_id,province,city,district,town,aedzid order by salesamount desc) rank\n" +
                    "FROM  \n" +
                    "(SELECT  \n" +
                    "region_id,shopid,sellcount,salesamount,timestamp,platformid \n" +
                    "FROM \"" + schem2 + "\".\"" + name2 + "\" \n" +
                    "WHERE platformid  ='" + platformid + "' AND timeStamp  ='" + timestamp + "') t1\n" +
                    "left JOIN\n" +
                    "(select shopId,shopName,name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address from \"" + schem3 + "\".\"" + name3 + "\") t2 \n" +
                    "ON t1.shopId = t2.shopId) where rank <= 300");

            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    public static void o2o_bestsell_shop_APP_jd_insert(Connection conn,
                                                       String schem1, String name1,
                                                       String schem2, String name2,
                                                       String schem3, String name3,
                                                       String platformid, String timestamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address)\n" +
                    "SELECT\n" +
                    "distinct region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "region_id,t1.shopid,sellcount,salesamount,timestamp,platformid,shopName,name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address, \n" +
                    "row_number() over(partition by region_id,province,city,district,town,aedzid order by salesamount desc) rank\n" +
                    "FROM  \n" +
                    "(SELECT  \n" +
                    "region_id,shopid,sellcount,salesamount,timestamp,platformid \n" +
                    "FROM \"" + schem2 + "\".\"" + name2 + "\" \n" +
                    "WHERE platformid  ='" + platformid + "' AND timeStamp  ='" + timestamp + "') t1\n" +
                    "left JOIN\n" +
                    "(select shopId,shopName,name,'' as   creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address from \"" + schem3 + "\".\"" + name3 + "\") t2 \n" +
                    "ON t1.shopId = t2.shopId) where rank <= 300");

            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入 大众点评畅销店铺表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param platformid
     * @param timestamp
     */
    public static void o2o_bestsell_shop_APP_dazhongdp_insert(Connection conn,
                                                              String schem1, String name1,
                                                              String schem2, String name2,
                                                              String schem3, String name3,
                                                              String platformid, String timestamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,latitude,longitude,shopurl,province,city,district,address)\n" +
                    "SELECT\n" +
                    "distinct region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,latitude,longitude,shopurl,province,city,district,address\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "region_id,t1.shopid,sellcount,salesamount,timestamp,platformid,shopName,latitude,longitude,shopurl,province,city,district,address, \n" +
                    "row_number() over(partition by region_id,province,city,district order by salesamount desc) rank\n" +
                    "FROM  \n" +
                    "(SELECT  \n" +
                    "region_id,shopid,sellcount,salesamount,timestamp,platformid \n" +
                    "FROM \"" + schem2 + "\".\"" + name2 + "\" \n" +
                    "WHERE platformid  ='" + platformid + "' AND timestamp  ='" + timestamp + "') t1\n" +
                    "left JOIN\n" +
                    "(select shopId,shopName,latitude,longitude,shopurl,province,city,district,address from \"" + schem3 + "\".\"" + name3 + "\") t2\n" +
                    "ON t1.shopId = t2.shopId) where rank <= 300");

            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入 美团，饿了么畅销店铺表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param platformid
     * @param timestamp
     */
    public static void o2o_bestsell_shop_APP_meituan_elm_insert(Connection conn,
                                                                String schem1, String name1,
                                                                String schem2, String name2,
                                                                String schem3, String name3,
                                                                String platformid, String timestamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,latitude,longitude,shopurl,province,city,district,address)\n" +
                    "SELECT\n" +
                    "distinct region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,latitude,longitude,shopurl,province,city,district,address\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "region_id,t1.shopid,sellcount,salesamount,timestamp,platformid,shopName,latitude,longitude,shopurl,province,city,district,address,\n" +
                    "row_number() over(partition by region_id,province,city,district order by salesamount desc) rank\n" +
                    "FROM  \n" +
                    "(SELECT  \n" +
                    "region_id,shopid,sellcount,salesamount,timestamp,platformid \n" +
                    "FROM \"" + schem2 + "\".\"" + name2 + "\" \n" +
                    "WHERE platformid  ='" + platformid + "' AND timestamp  ='" + timestamp + "' --and length(shopid)=32\n" +
                    ") t1\n" +
                    "left JOIN\n" +
                    "(select shopId,shopName,latitude,longitude,shopurl,province,city,district,address from \"" + schem3 + "\".\"" + name3 + "\" --where length(shopId)=32\n" +
                    ") t2\n" +
                    "ON t1.shopId = t2.shopId) where rank <= 300");

            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入 苏宁畅销店铺表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param platformid
     * @param timestamp
     */
    public static void o2o_bestsell_shop_APP_suning_insert(Connection conn,
                                                           String schem1, String name1,
                                                           String schem2, String name2,
                                                           String schem3, String name3,
                                                           String platformid, String timestamp
    ) {
        PreparedStatement pst = null;
        try {

            String sql = "INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,latitude,longitude,shopurl,province,city,district,town,aedzid,address)\n" +
                    "SELECT\n" +
                    "distinct region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,latitude,longitude,shopurl,province,city,district,town,aedzid,address\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "region_id,t1.shopid,sellcount,salesamount,timestamp,platformid,shopName,name,latitude,longitude,shopurl,province,city,district,town,aedzid,address, \n" +
                    "row_number() over(partition by region_id,province,city,district,town,aedzid order by salesamount desc) rank\n" +
                    "FROM  \n" +
                    "(SELECT  \n" +
                    "region_id,shopid,sellcount,salesamount,timestamp,platformid \n" +
                    "FROM \"" + schem2 + "\".\"" + name2 + "\" \n" +
                    "WHERE platformid  ='" + platformid + "' AND timestamp  ='" + timestamp + "') t1\n" +
                    "left JOIN\n" +
                    "(select shopId,shopName,name,latitude,longitude,shopurl,province,city,district,town,aedzid,address from \"" + schem3 + "\".\"" + name3 + "\") t2\n" +
                    "ON t1.shopId = t2.shopId) where rank <= 300";
            System.out.println(sql);
            pst = conn.prepareStatement(sql);
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入 拼多多畅销店铺表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param platformid
     * @param timestamp
     */
    public static void o2o_bestsell_shop_APP_pinduoduo_insert(Connection conn,
                                                              String schem1, String name1,
                                                              String schem2, String name2,
                                                              String schem3, String name3,
                                                              String platformid, String timestamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,shopurl,province,city,district,address)\n" +
                    "SELECT\n" +
                    "distinct region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,shopurl,province,city,district,address\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "region_id,t1.shopid,sellcount,salesamount,timestamp,platformid,shopName,name,shopurl,province,city,district,address, \n" +
                    "row_number() over(partition by region_id,province,city,district order by salesamount desc) rank\n" +
                    "FROM  \n" +
                    "(SELECT  \n" +
                    "region_id,shopid,sellcount,salesamount,timestamp,platformid \n" +
                    "FROM \"" + schem2 + "\".\"" + name2 + "\" \n" +
                    "WHERE platformid  ='" + platformid + "' AND timestamp  ='" + timestamp + "') t1\n" +
                    "left JOIN\n" +
                    "(select shopId,shopName,cast('0' as text) as name,shopurl,province,city,district,address from \"" + schem3 + "\".\"" + name3 + "\") t2\n" +
                    "ON t1.shopId = t2.shopId) where rank <= 300");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入 美团团购畅销店铺表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param platformid
     * @param timestamp
     */
    public static void o2o_bestsell_shop_APP_meituan_tg_insert(Connection conn,
                                                               String schem1, String name1,
                                                               String schem2, String name2,
                                                               String schem3, String name3,
                                                               String platformid, String timestamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,latitude,longitude,shopurl,province,city,district,address)\n" +
                    "SELECT\n" +
                    "distinct region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,latitude,longitude,shopurl,province,city,district,address\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "region_id,t1.shopid,sellcount,salesamount,timestamp,platformid,shopName,latitude,longitude,shopurl,province,city,district,address, \n" +
                    "row_number() over(partition by region_id,province,city,district order by salesamount desc) rank\n" +
                    "FROM  \n" +
                    "(SELECT  \n" +
                    "region_id,shopid,sellcount,salesamount,timestamp,platformid \n" +
                    "FROM \"" + schem2 + "\".\"" + name2 + "\" \n" +
                    "WHERE platformid  ='" + platformid + "' AND timestamp  ='" + timestamp + "') t1\n" +
                    "left JOIN\n" +
                    "(select shopId,shopName,latitude,longitude,shopurl,province,city,district,address from \"" + schem3 + "\".\"" + name3 + "\") t2\n" +
                    "ON t1.shopId = t2.shopId) where rank <= 300");
            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入 hema 畅销店铺表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param platformid
     * @param timestamp
     */
    public static void o2o_bestsell_shop_APP_hmxs_insert(Connection conn,
                                                         String schem1, String name1,
                                                         String schem2, String name2,
                                                         String schem3, String name3,
                                                         String platformid, String timestamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\" \n" +
                    "(region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address)\n" +
                    "SELECT\n" +
                    "distinct region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "region_id,t1.shopid,sellcount,salesamount,timestamp,platformid,shopName,name,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address, \n" +
                    "row_number() over(partition by region_id,province,city,district,town,aedzid order by salesamount desc) rank\n" +
                    "FROM  \n" +
                    "(SELECT  \n" +
                    "region_id,shopid,sellcount,salesamount,timestamp,platformid \n" +
                    "FROM \"" + schem2 + "\".\"" + name2 + "\" \n" +
                    "WHERE \n" +
                    "(\n" +
                    "platformid = '46' or \n" +
                    "platformid = '45' or \n" +
                    "platformid = '49' or \n" +
                    "platformid = '47' or \n" +
                    "platformid = '48' or \n" +
                    "platformid = '52' or \n" +
                    "platformid = '38' or \n" +
                    "platformid = '40' or \n" +
                    "platformid = '51' or \n" +
                    "platformid = '42' or \n" +
                    "platformid = '39' or \n" +
                    "platformid = '50' or \n" +
                    "platformid = '43' or \n" +
                    "platformid = '41' or \n" +
                    "platformid = '100' or \n" +
                    "platformid = '102' or \n" +
                    "platformid = '104' or \n" +
                    "platformid = '105' or \n" +
                    "platformid = '106' or \n" +
                    "platformid = '107' or \n" +
                    "platformid = '108' or \n" +
                    "platformid = '109' or \n" +
                    "platformid = '111' or \n" +
                    "platformid = '112' or \n" +
                    "platformid = '113' or \n" +
                    "platformid = '114' or \n" +
                    "platformid = '116' or \n" +
                    "platformid = '117' or \n" +
                    "platformid = '118' or \n" +
                    "platformid = '119' or \n" +
                    "platformid = '121' or \n" +
                    "platformid = '122' or \n" +
                    "platformid = '124' or \n" +
                    "platformid = '126' or \n" +
                    "platformid = '101' or \n" +
                    "platformid = '110' or \n" +
                    "platformid = '103' or \n" +
                    "platformid = '115' or \n" +
                    "platformid = '127' or \n" +
                    "platformid = '128' or \n" +
                    "platformid = '129' or \n" +
                    "platformid = '130' or \n" +
                    "platformid = '131' or \n" +
                    "platformid = '132' or \n" +
                    "platformid = '133' or \n" +
                    "platformid = '134' or \n" +
                    "platformid = '135' or \n" +
                    "platformid = '136' or \n" +
                    "platformid = '137' or \n" +
                    "platformid = '138' or \n" +
                    "platformid = '140' or \n" +
                    "platformid = '141' or \n" +
                    "platformid = '139' \n" +
                    ") \n" +
                    " AND timestamp  ='" + timestamp + "') t1\n" +
                    "left JOIN\n" +
                    "(select shopId,shopName,name,cast('-1' as text) as latitude,cast('-1' as text) as longitude,cast('-1' as text) as shopurl,province,city,district,cast('-1' as text) as town,cast('-1' as text) as aedzid,cast('-1' as text) as starts,address \n" +
                    "from \"" + schem3 + "\".\"" + name3 + "\") t2 \n" +
                    "ON t1.shopId = t2.shopId) where rank <= 300");
            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入 淘宝畅销店铺表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param platformid
     * @param timestamp
     */
    public static void o2o_bestsell_shop_APP_taobao_insert(Connection conn,
                                                           String schem1, String name1,
                                                           String schem2, String name2,
                                                           String schem3, String name3,
                                                           String platformid, String timestamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\" \n" +
                    "(region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address)\n" +
                    "SELECT\n" +
                    "distinct region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "region_id,t1.shopid,sellcount,salesamount,timestamp,platformid,shopName,name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address, \n" +
                    "row_number() over(partition by region_id,province,city,district,town,aedzid order by salesamount desc) rank\n" +
                    "FROM  \n" +
                    "(SELECT  \n" +
                    "region_id,shopid,sellcount,salesamount,timestamp,platformid \n" +
                    "FROM \"" + schem2 + "\".\"" + name2 + "\" \n" +
                    "WHERE (platformid = '" + platformid + "') AND timestamp  ='" + timestamp + "') t1\n" +
                    "left JOIN\n" +
                    "(select shopId,shopName,name,cast('-1' as text) as latitude,cast('-1' as text) as longitude,shopurl,province,city,district," +
                    "cast('-1' as text) as town,cast('-1' as text) as aedzid,cast('-1' as text) as starts,address,creditlevel \n" +
                    "from \"" + schem3 + "\".\"" + name3 + "\") t2 \n" +
                    "ON t1.shopId = t2.shopId) where rank <= 300");
            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入 天猫，京东人气店铺表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param platformid
     * @param timestamp
     */
    public static void o2o_sentiment_shop_APP_tm_jd_insert(Connection conn,
                                                           String schem1, String name1,
                                                           String schem2, String name2,
                                                           String schem3, String name3,
                                                           String platformid, String timestamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fans,name,latitude,longitude,shopurl,province,city,district,town,aedzId,starts,evaluates,address)\n" +
                    "SELECT \n" +
                    "shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,name,latitude,longitude,shopurl,province,city,district,town,aedzId,starts,evaluates,address\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,name,latitude,longitude,shopurl,province,city,district,town,aedzId,starts,evaluates,address,\n" +
                    "row_number() over(partition by platformid,region_id,province,city,district,town,aedzId order by fan desc ) rank\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "t1.shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,name,latitude,longitude,shopurl,province,city,district,town,aedzId,starts,evaluates,address\n" +
                    "FROM  \n" +
                    "(SELECT  \n" +
                    "region_id,shopid,sellcount,salesamount,timestamp,platformid\n" +
                    "FROM \"" + schem2 + "\".\"" + name2 + "\"\n" +
                    "WHERE platformid  ='" + platformid + "' AND timestamp  = '" + timestamp + "') t1\n" +
                    "LEFT JOIN\n" +
                    "(select shopId,shopName,cast(fans as int8) fan,name,latitude,longitude,shopurl,province,city,district,town,aedzId,starts,evaluates,address \n" +
                    "from  \"" + schem3 + "\".\"" + name3 + "\") t2\n" +
                    "ON t1.shopId = t2.shopId) t3) WHERE rank <=300");
            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入 美团，饿了么，大众点评人气店铺表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param platformid
     * @param timestamp
     */
    public static void o2o_sentiment_shop_APP_meituan_elm_dazhongdp_insert(Connection conn,
                                                                           String schem1, String name1,
                                                                           String schem2, String name2,
                                                                           String schem3, String name3,
                                                                           String platformid, String timestamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fans,latitude,longitude,shopurl,province,city,district,evaluates,address)\n" +
                    "SELECT \n" +
                    "shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,latitude,longitude,shopurl,province,city,district,evaluates,address\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,latitude,longitude,shopurl,province,city,district,evaluates,address,\n" +
                    "row_number() over(partition by platformid,region_id,province,city,district order by fan desc ) rank\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "t1.shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,latitude,longitude,shopurl,province,city,district,evaluates,address\n" +
                    "FROM  \n" +
                    "(SELECT  \n" +
                    "region_id,shopid,sellcount,salesamount,timestamp,platformid\n" +
                    "FROM \"" + schem2 + "\".\"" + name2 + "\"\n" +
                    "WHERE platformid  ='" + platformid + "' AND timestamp  ='" + timestamp + "' --and length(shopid)=32\n" +
                    ") t1\n" +
                    "LEFT JOIN\n" +
                    "(select shopId,shopName,cast((case when shopCommentCount ='None' then '0' else shopCommentCount end) as int8) fan,latitude,longitude,shopurl,province,city,district,evaluates,address \n" +
                    "from \"" + schem3 + "\".\"" + name3 + "\"  --where length(shopId)=32\n" +
                    ") t2\n" +
                    "ON t1.shopId = t2.shopId) t3) WHERE rank <=300");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入 美团团购人气店铺表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param platformid
     * @param timestamp
     */
    public static void o2o_sentiment_shop_APP_meituan_tg_insert(Connection conn,
                                                                String schem1, String name1,
                                                                String schem2, String name2,
                                                                String schem3, String name3,
                                                                String platformid, String timestamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fans,latitude,longitude,shopurl,province,city,district,evaluates,address)\n" +
                    "SELECT \n" +
                    "shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,latitude,longitude,shopurl,province,city,district,evaluates,address\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,latitude,longitude,shopurl,province,city,district,evaluates,address,\n" +
                    "row_number() over(partition by platformid,region_id,province,city,district order by fan desc ) rank\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "t1.shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,latitude,longitude,shopurl,province,city,district,evaluates,address\n" +
                    "FROM  \n" +
                    "(SELECT  \n" +
                    "region_id,shopid,sellcount,salesamount,timestamp,platformid\n" +
                    "FROM \"" + schem2 + "\".\"" + name2 + "\"\n" +
                    "WHERE platformid  ='" + platformid + "' AND timestamp  ='" + timestamp + "') t1\n" +
                    "LEFT JOIN\n" +
                    "(select shopId,shopName,cast(shopCommentCount as int8) fan,latitude,longitude,shopurl,province,city,district,evaluates,address \n" +
                    "from \"" + schem3 + "\".\"" + name3 + "\") t2\n" +
                    "ON t1.shopId = t2.shopId) t3) WHERE rank <=300");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入 淘宝人气店铺表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param platformid
     * @param timestamp
     */
    public static void o2o_sentiment_shop_APP_taobao_insert(Connection conn,
                                                            String schem1, String name1,
                                                            String schem2, String name2,
                                                            String schem3, String name3,
                                                            String platformid, String timestamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fans,name,shopurl,province,city,district,starts,evaluates,address)\n" +
                    "SELECT \n" +
                    "shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,name,shopurl,province,city,district,starts,evaluates,address\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,name,shopurl,province,city,district,starts,evaluates,address,\n" +
                    "row_number() over(partition by platformid,region_id,province,city,district order by fan desc ) rank\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "t1.shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,name,shopurl,province,city,district,starts,evaluates,address\n" +
                    "FROM  \n" +
                    "(SELECT  \n" +
                    "region_id,shopid,sellcount,salesamount,timestamp,platformid\n" +
                    "FROM \"" + schem2 + "\".\"" + name2 + "\"\n" +
                    "WHERE platformid  ='" + platformid + "' AND timestamp  ='" + timestamp + "') t1\n" +
                    "LEFT JOIN\n" +
                    "(select shopId,shopName,cast(fans as int8) fan,name,shopurl,province,city,district,starts,evaluates,address from \"" + schem3 + "\".\"" + name3 + "\") t2\n" +
                    "ON t1.shopId = t2.shopId) t3) WHERE rank <=300");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 创建 淘宝商品修改好region_id的表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param timestamp
     */
    public static void o2o_bestsell_product_APP_taobao_create(Connection conn,
                                                              String schem1, String name1,
                                                              String timestamp
    ) {
        Statement stmt = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            stmt = conn.createStatement();
            //执行普通SQL语句。
            int rc = stmt.executeUpdate(
                    /**
                     *1.修改region_id
                     *
                     */
                    "create OR REPLACE view \"dbadmin\".\"g2019_taobao_" + timestamp + "\" as\n" +
                            "select\n" +
                            "distinct\n" +
                            "t1.is_oversea,\n" +
                            "t1.province,\n" +
                            "t1.city,\n" +
                            "t1.district,\n" +
                            "'' as origincountryname,\n" +
                            "t1.originproduction,\n" +
                            "t1.good_id,\n" +
                            "t1.title,\n" +
                            "t1.goodurl,\n" +
                            "t1.images,\n" +
                            "t1.salesamount,\n" +
                            "t1.sellcount,\n" +
                            "t1.fans,\n" +
                            "t1.favcount,\n" +
                            "t1.brandname_cn,\n" +
                            "t1.name,\n" +
                            "t1.shopname,\n" +
                            "t1.shopurl,\n" +
                            "t1.platformid,\n" +
                            "'' as start_time,\n" +
                            "'' as end_time,\n" +
                            "t1.firstcategoryid,\n" +
                            "t1.secondcategoryid,\n" +
                            "t1.thirdcategoryid,\n" +
                            "t1.brandname_en,\n" +
                            "t1.regional_id,\n" +
                            "'' town,\n" +
                            "'' aedzid,\n" +
                            "t1.starts,\n" +
                            "t1.address,\n" +
                            "t1.evaluates,\n" +
                            "t1.timestamp,\n" +
                            "t1.shopid,\n" +
                            "t1.products,\n" +
                            "t2.uniqueid reg_id\n" +
                            "from\n" +
                            "(select \n" +
                            "*,\n" +
                            "case \n" +
                            "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n" +
                            "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n" +
                            "else district end  districts\n" +
                            //\""+schem1+"\".\""+name1+"\"
                            "from \"" + schem1 + "\".\"" + name1 + "\" where timestamp='"+timestamp+"') t1\n" +
                            "left join\n" +
                            "(select province,city,district,uniqueid from  dbadmin.address_mapping_p_c_d_show_2018 where timestamp = '" + timestamp + "') t2\n" +
                            "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)"
            );
            stmt.close();
        } catch (SQLException e) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 创建 拼多多农商品修改好region_id的表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param timestamp
     */
    public static void o2o_bestsell_product_APP_pinduoduo_create(Connection conn,
                                                                 String schem1, String name1,
                                                                 String timestamp
    ) {
        Statement stmt = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            stmt = conn.createStatement();
            //执行普通SQL语句。
            int rc = stmt.executeUpdate(
                    /**
                     *1.修改region_id
                     *
                     */
                    "create temporary table \"g2019_pinduoduo\" as\n" +
                            "select\n" +
                            "distinct\n" +
                            "'false' as \"is_oversea\",\n" +
                            "t1.province,\n" +
                            "t1.city,\n" +
                            "t1.district,\n" +
                            "'' as origincountryname,\n" +
                            "'' as originproduction,\n" +
                            "t1.good_id,\n" +
                            "t1.title,\n" +
                            "'' as goodurl,\n" +
                            "t1.images,\n" +
                            "t1.salesamount,\n" +
                            "t1.sellcount,\n" +
                            "'' as fans,\n" +
                            "'' as favcount,\n" +
                            "'' as brandname_cn,\n" +
                            "'' as name,\n" +
//                            "'' as shopname,\n" +
                            "t1.shopname,\n" +
                            "'' as shopurl,\n" +
                            "t1.platformid,\n" +
                            "'' as start_time,\n" +
                            "'' as end_time,\n" +
                            "t1.firstcategoryid,\n" +
                            "t1.secondcategoryid,\n" +
                            "t1.thirdcategoryid,\n" +
                            "'' as brandname_en,\n" +
                            "t1.regional_id,\n" +
                            "'' as town,\n" +
                            "'' as aedzid,\n" +
                            "'' as starts,\n" +
                            "t1.address,\n" +
                            "t1.evaluates,\n" +
                            "t1.\"timestamp\",\n" +
                            "t1.shopid,\n" +
                            "t1.products,\n" +
                            "t2.uniqueid reg_id\n" +
                            "from\n" +
                            "(select \n" +
                            "*,\n" +
                            "case \n" +
                            "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n" +
                            "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n" +
                            "else district end  districts\n" +
                            "from \"" + schem1 + "\".\"" + name1 + "\") t1\n" +
                            "left join\n" +
                            "(select province,city,district,uniqueid from  dbadmin.\"address_mapping_p_c_d_show_2018\" where timestamp = '" + timestamp + "') t2\n" +
                            "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)"
            );
            stmt.close();
        } catch (SQLException e) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 创建 京东商品修改好region_id的表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param timestamp
     */
    public static void o2o_bestsell_product_APP_jd_create(Connection conn,
                                                          String schem1, String name1,
                                                          String timestamp
    ) {
        Statement stmt = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            stmt = conn.createStatement();
            //执行普通SQL语句。
            int rc = stmt.executeUpdate(
                    /**
                     *1.修改region_id
                     *
                     */
                    "create temporary table \"g2019_jd\" as\n" +
                            "select\n" +
                            "distinct\n" +
                            "t1.\"is_oversea\",\n" +
                            "t1.province,\n" +
                            "t1.city,\n" +
                            "t1.district,\n" +
                            "t1.origincountryname,\n" +
                            "t1.originproduction,\n" +
                            "t1.good_id,\n" +
                            "t1.title,\n" +
                            "t1.goodurl,\n" +
                            "t1.images,\n" +
                            "t1.salesamount,\n" +
                            "t1.sellcount,\n" +
                            "t1.fans,\n" +
                            "'' as favcount,\n" +
                            "t1.brandname_cn,\n" +
                            "t1.name,\n" +
                            "t1.shopname,\n" +
                            "t1.shopurl,\n" +
                            "t1.platformid,\n" +
                            "'' as start_time,\n" +
                            "'' as end_time,\n" +
                            "t1.firstcategoryid,\n" +
                            "t1.secondcategoryid,\n" +
                            "t1.thirdcategoryid,\n" +
                            "t1.brandname_en,\n" +
                            "t1.regional_id,\n" +
                            "t1.town,\n" +
                            "t1.aedzid,\n" +
                            "t1.starts,\n" +
                            "t1.address,\n" +
                            "t1.evaluates,\n" +
                            "t1.\"timestamp\",\n" +
                            "t1.shopid,\n" +
                            "t3.products,\n" +
                            "t2.uniqueid reg_id\n" +
                            "from\n" +
                            "(select \n" +
                            "*,\n" +
                            "case \n" +
                            "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then town \n" +
                            "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市' or city = '东营市') then aedzid\n" +
                            "else district end  districts\n" +
                            "from \"" + schem1 + "\".\"" + name1 + "\") t1\n" +
                            "left join\n" +
                            "(select province,city,district,uniqueid from  dbadmin.\"address_mapping_p_c_d_show_2018\" where timestamp = '" + timestamp + "') t2\n" +
                            "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)" +
                            "left join\n" +
                            "(SELECT thirdcategoryid,products FROM dbadmin.\"O2O_stand_products_IDL\" WHERE TIMESTAMP = '" + timestamp + "' \n" +
                            "  AND flag_product_level = '2' \n" +
                            "GROUP BY\n" +
                            "  thirdcategoryid,products)  t3\n" +
                            "  on t1.thirdcategoryid=t3.thirdcategoryid"
            );
            stmt.close();
        } catch (SQLException e) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 创建 天猫商品修改好region_id的表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param timestamp
     */
    public static void o2o_bestsell_product_APP_tmall_create(Connection conn,
                                                             String schem1, String name1,
                                                             String timestamp
    ) {
        Statement stmt = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            stmt = conn.createStatement();
            //执行普通SQL语句。
            int rc = stmt.executeUpdate(
                    /**
                     *1.修改region_id
                     *
                     */
                    "create temporary table \"g2019_tmall\" as\n" +
                            "select\n" +
                            "distinct\n" +
                            "t1.\"is_oversea\",\n" +
                            "t1.province,\n" +
                            "t1.city,\n" +
                            "t1.district,\n" +
                            "t1.origincountryname,\n" +
                            "t1.originproduction,\n" +
                            "t1.good_id,\n" +
                            "t1.title,\n" +
                            "t1.goodurl,\n" +
                            "t1.images,\n" +
                            "t1.salesamount,\n" +
                            "t1.sellcount,\n" +
                            "t1.fans,\n" +
                            "t1.favcount,\n" +
                            "t1.brandname_cn,\n" +
                            "t1.name,\n" +
                            "t1.shopname,\n" +
                            "t1.shopurl,\n" +
                            "t1.platformid,\n" +
                            "'' as start_time,\n" +
                            "'' as end_time,\n" +
                            "t1.firstcategoryid,\n" +
                            "t1.secondcategoryid,\n" +
                            "t1.thirdcategoryid,\n" +
                            "t1.brandname_en,\n" +
                            "t1.regional_id,\n" +
                            "t1.town,\n" +
                            "t1.aedzid,\n" +
                            "t1.starts,\n" +
                            "t1.address,\n" +
                            "t1.evaluates,\n" +
                            "t1.\"timestamp\",\n" +
                            "t1.shopid,\n" +
                            "t1.products,\n" +
                            "t2.uniqueid reg_id\n" +
                            "from\n" +
                            "(select \n" +
                            "*,\n" +
                            "case \n" +
                            "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then town \n" +
                            "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市' or city = '东营市') then aedzid\n" +
                            "else district end  districts\n" +
                            "from \"" + schem1 + "\".\"" + name1 + "\" where timestamp='"+timestamp+"') t1\n" +
                            "left join\n" +
                            "(select province,city,district,uniqueid from  dbadmin.\"address_mapping_p_c_d_show_2018\" where timestamp = '" + timestamp + "') t2\n" +
                            "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)"
            );
            stmt.close();
        } catch (SQLException e) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 创建 苏宁商品修改好region_id的表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param timestamp
     */
    public static void o2o_bestsell_product_APP_suning_create(Connection conn,
                                                              String schem1, String name1,
                                                              String timestamp
    ) {
        Statement stmt = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            stmt = conn.createStatement();
            //执行普通SQL语句。
            int rc = stmt.executeUpdate(
                    /**
                     *1.修改region_id
                     *
                     */
                    "create temporary table \"g2019_suning\" as\n" +
                            "select\n" +
                            "distinct\n" +
                            "t1.\"is_oversea\",\n" +
                            "t1.province,\n" +
                            "t1.city,\n" +
                            "t1.district,\n" +
                            "t1.origincountryname,\n" +
                            "t1.originproduction,\n" +
                            "t1.good_id,\n" +
                            "t1.title,\n" +
                            "t1.goodurl,\n" +
                            "t1.images,\n" +
                            "t1.salesamount,\n" +
                            "t1.sellcount,\n" +
                            "'' as fans,\n" +
                            "'' as favcount,\n" +
                            "t1.brandname_cn,\n" +
                            "t1.name,\n" +
                            "t1.shopname,\n" +
                            "t1.shopurl,\n" +
                            "t1.platformid,\n" +
                            "'' as start_time,\n" +
                            "'' as end_time,\n" +
                            "t1.firstcategoryid,\n" +
                            "t1.secondcategoryid,\n" +
                            "t1.thirdcategoryid,\n" +
                            "t1.brandname_en,\n" +
                            "t1.regional_id,\n" +
                            "t1.town,\n" +
                            "t1.aedzid,\n" +
                            "'' as starts,\n" +
                            "t1.address,\n" +
                            "t1.evaluates,\n" +
                            "t1.\"timestamp\",\n" +
                            "t1.shopid,\n" +
                            "t1.products,\n" +
                            "t2.uniqueid reg_id\n" +
                            "from\n" +
                            "(select \n" +
                            "*,\n" +
                            "case \n" +
                            "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then town \n" +
                            "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市' or city = '东营市') then aedzid\n" +
                            "else district end  districts\n" +
                            "from \"" + schem1 + "\".\"" + name1 + "\") t1\n" +
                            "left join\n" +
                            "(select province,city,district,uniqueid from  dbadmin.\"address_mapping_p_c_d_show_2018\" where timestamp = '" + timestamp + "') t2\n" +
                            "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)"
            );
            stmt.close();
        } catch (SQLException e) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入 淘宝畅销单品表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     */
    public static void o2o_bestsell_product_APP_taobao_insert(Connection conn,
                                                              String schem1, String name1, String timeStamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(good_id,sellCount,salesAmount,platformid,timeStamp,region_id,shopid,shopname,shopurl,title,goodurl,name)\n" +
                    "select\n" +
                    "t1.good_id,t1.sellcounts,t1.sales,t1.platformId,t1.timestamp,t1.reg_id,t1.shopid,t1.shopname,t1.shopurl,t1.title,t1.goodurl,t1.name\n" +
                    "from\n" +
                    "(select\n" +
                    "*,row_number() over(partition by reg_id,province,city,district order by sales desc) rank\n" +
                    "from\n" +
                    "(SELECT good_id,cast(cast(sellCount as float8) as bigint) as sellcounts,cast(salesamount as float8) sales,platformId,province,city,district,\n" +
                    "timestamp,reg_id,shopid,shopname,shopurl,title,goodurl,'' as name from \"dbadmin\".\"g2019_taobao_" + timeStamp + "\" where good_id is not null and\n" +
                    "products ='1001'\n" +
                    "and province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛'))) t1\n" +
                    "where t1.rank <= 150");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入 拼多多畅销单品表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     */
    public static void o2o_bestsell_product_APP_pinduoduo_insert(Connection conn,
                                                                 String schem1, String name1
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(good_id,sellCount,salesAmount,platformid,timeStamp,region_id,shopid,shopname,shopurl,title,goodurl,name)\n" +
                    "select\n" +
                    "t1.good_id,t1.sellcounts,t1.sales,t1.platformId,t1.timestamp,t1.reg_id,t1.shopid,t1.shopname,t1.shopurl,t1.title,t1.goodurl,t1.name\n" +
                    "from\n" +
                    "(select\n" +
                    "*,row_number() over(partition by reg_id,province,city,district order by sales desc) rank\n" +
                    "from\n" +
                    "(SELECT good_id,cast(cast(sellCount as float8) as bigint) as sellcounts,cast(salesamount as float8) sales,platformId,province,city,district,\n" +
                    "timestamp,reg_id,shopid,shopname,shopurl,title,goodurl,'' as name from g2019_pinduoduo where good_id is not null and\n" +
                    "products ='1001'  and is_oversea != 'true'\n" +
                    "and province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛'))) t1\n" +
                    "where t1.rank <= 150");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            System.out.println("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入 京东平台畅销单品表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     */
    public static void o2o_bestsell_product_APP_jd_insert(Connection conn,
                                                          String schem1, String name1
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(good_id,sellCount,salesAmount,platformid,timeStamp,region_id,shopid,shopname,shopurl,title,goodurl,name)\n" +
                    "select\n" +
                    "t1.good_id,t1.sellcounts,t1.sales,t1.platformId,t1.timestamp,t1.reg_id,t1.shopid,t1.shopname,t1.shopurl,t1.title,t1.goodurl,t1.name\n" +
                    "from\n" +
                    "(select\n" +
                    "*,row_number() over(partition by reg_id,province,city,district order by sales desc) rank\n" +
                    "from\n" +
                    "(SELECT good_id,cast(cast(sellCount as float8) as bigint) as sellcounts,cast(salesamount as float8) sales,platformId,province,city,district,\n" +
                    "timestamp,reg_id,shopid,shopname,shopurl,title,goodurl,name from g2019_jd where good_id is not null and\n" +
                    "products ='1001'  and is_oversea != 'true'\n" +
                    "and province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛'))) t1\n" +
                    "where t1.rank <= 150");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            System.out.println("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入 天猫平台畅销单品表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     */
    public static void o2o_bestsell_product_APP_tmall_insert(Connection conn,
                                                             String schem1, String name1
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(good_id,sellCount,salesAmount,platformid,timeStamp,region_id,shopid,shopname,shopurl,title,goodurl,name)\n" +
                    "select\n" +
                    "t1.good_id,t1.sellcounts,t1.sales,t1.platformId,t1.timestamp,t1.reg_id,t1.shopid,t1.shopname,t1.shopurl,t1.title,t1.goodurl,t1.name\n" +
                    "from\n" +
                    "(select\n" +
                    "*,row_number() over(partition by reg_id,province,city,district order by sales desc) rank\n" +
                    "from\n" +
                    "(SELECT good_id,cast(cast(sellCount as float8) as bigint) as sellcounts,cast(salesamount as float8) sales,platformId,province,city,district,\n" +
                    "timestamp,reg_id,shopid,shopname,shopurl,title,goodurl,name from g2019_tmall where good_id is not null and\n" +
                    "products ='1001'  and is_oversea != 'true'\n" +
                    "and province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛'))) t1\n" +
                    "where t1.rank <= 150");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入 苏宁平台畅销单品表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     */
    public static void o2o_bestsell_product_APP_suning_insert(Connection conn,
                                                              String schem1, String name1
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(good_id,sellCount,salesAmount,platformid,timeStamp,region_id,shopid,shopname,shopurl,title,goodurl,name)\n" +
                    "select\n" +
                    "t1.good_id,t1.sellcounts,t1.sales,t1.platformId,t1.timestamp,t1.reg_id,t1.shopid,t1.shopname,t1.shopurl,t1.title,t1.goodurl,t1.name\n" +
                    "from\n" +
                    "(select\n" +
                    "*,row_number() over(partition by reg_id,province,city,district order by sales desc) rank\n" +
                    "from\n" +
                    "(SELECT good_id,cast(cast(sellCount as float8) as bigint) as sellcounts,cast(salesamount as float8) sales,platformId,province,city,district,\n" +
                    "timestamp,reg_id,shopid,shopname,shopurl,title,goodurl,name from g2019_suning where good_id is not null and\n" +
                    "products ='1001'  and is_oversea != 'true'\n" +
                    "and province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛'))) t1\n" +
                    "where t1.rank <= 150");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入 淘宝人气单品表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     */
    public static void o2o_sentiment_product_APP_taobao_insert(Connection conn,
                                                               String schem1, String name1, String timeStamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(good_id,sellCount,favcount,salesAmount,platformid,timeStamp,region_id,shopid,shopname,shopurl,title,goodurl,name)\n" +
                    "SELECT\n" +
                    "t1.good_id,t1.sellcounts,t1.favs,t1.salesamount,t1.platformId,t1.timestamp,t1.reg_id,t1.shopid,t1.shopname,t1.shopurl,t1.title,t1.goodurl,t1.name\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "*,row_number() over(partition by reg_id,province,city,district order by favs desc) rank\n" +
                    "FROM \n" +
                    "(SELECT good_id,cast(cast(sellCount as float8) as bigint) as sellcounts,salesamount,cast(favcount as int8) favs,platformId,province,city,district,\n" +
                    "timestamp,reg_id,shopid,shopname,shopurl,title,goodurl,'' as name from \"dbadmin\".\"g2019_taobao_" + timeStamp + "\" where good_id is not null and\n" +
                    "products ='1001'\n" +
                    "and province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛'))) t1\n" +
                    "where t1.rank <= 150");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入 天猫人气单品表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     */
    public static void o2o_sentiment_product_APP_tmall_insert(Connection conn,
                                                              String schem1, String name1
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(good_id,sellCount,favcount,salesAmount,platformid,timeStamp,region_id,shopid,shopname,shopurl,title,goodurl,name)\n" +
                    "SELECT\n" +
                    "t1.good_id,t1.sellcounts,t1.favs,t1.salesamount,t1.platformId,t1.timestamp,t1.reg_id,t1.shopid,t1.shopname,t1.shopurl,t1.title,t1.goodurl,t1.name\n" +
                    "FROM\n" +
                    "(SELECT\n" +
                    "*,row_number() over(partition by reg_id,province,city,district order by favs desc) rank\n" +
                    "FROM \n" +
                    "(SELECT good_id,cast(cast(sellCount as float8) as bigint) as sellcounts,salesamount,cast(favcount as int8) favs,platformId,province,city,district,\n" +
                    "timestamp,reg_id,shopid,shopname,shopurl,title,goodurl,name from g2019_tmall where good_id is not null and\n" +
                    "products ='1001' and is_oversea != 'true'\n" +
                    "and province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛'))) t1\n" +
                    "where t1.rank <= 150");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入价格段表
     */
    public static void o2o_products_price_APP_2019_insert(Connection conn,
                                                          String schem1, String name1,
                                                          String schem2, String name2
    ) {
        PreparedStatement pst = null;
        try {

//            pst = conn.prepareStatement("insert into \"" + schem1 + "\".\"" + name1 + "\"\n" +
//                    "select distinct platformid,province,city,district,firstcategoryid,secondcategoryid,thirdcategoryid,\n" +
//                    "fourthcategoryid,sellcount,salesamount,flag_region_level,flag_cate_level,timestamp,sumPriceText,region_id,goodCount\n" +
//                    " from \n" +
//                    "(\n" +
//                    "select t3.platformid,t3.province,t3.city,t3.district,t3.firstcategoryid,t3.secondcategoryid,t3.thirdcategoryid,\n" +
//                    "t3.fourthcategoryid,t3.sellcount,t3.salesamount, '5' flag_region_level, '3' flag_cate_level,t3.timestamp,t4.sumPriceText,t4.goodCount,t5.uniqueid region_id\n" +
//                    "from \n" +
//                    "(\n" +
//                    "select t1.good_id,t1.platformid,t1.pricetext, t1.province,t1.city,t1.district,t1.firstcategoryid,\n" +
//                    "t1.secondcategoryid,t1.thirdcategoryid,t1.fourthcategoryid,t1.sellcount,t1.salesamount, '5' flag_region_level, '3' flag_cate_level,t1.timestamp\n" +
//                    "from " + schem2 + ".\"" + name2 + "\" t1\n" +
//                    "left join dbadmin.\"O2O_stand_products_IDL\" t2\n" +
//                    "on t1.thirdcategoryid = t2.thirdcategoryid and t1.platformid = t2.platformid and t1.timestamp = t2.timestamp \n" +
//                    "where t2.product_type = '1'\n" +
//                    ") t3 \n" +
//                    "left join \n" +
//                    "( \n" +
//                    "select province,city,district,sum(pricetext) sumPriceText, count(good_id) goodCount\n" +
//                    "from \n" +
//                    "(\n" +
//                    "select t1.good_id,t1.platformid,t1.pricetext, t1.province,t1.city,t1.district,t1.firstcategoryid,\n" +
//                    "t1.secondcategoryid,t1.thirdcategoryid,t1.fourthcategoryid,t1.sellcount,t1.salesamount, '5' flag_region_level, '3' flag_cate_level,t1.timestamp\n" +
//                    "from " + schem2 + ".\"" + name2 + "\" t1\n" +
//                    "left join dbadmin.\"O2O_stand_products_IDL\" t2\n" +
//                    "on t1.thirdcategoryid = t2.thirdcategoryid and t1.platformid = t2.platformid and t1.timestamp = t2.timestamp \n" +
//                    "where t2.product_type = '1'\n" +
//                    ")\n" +
//                    "group by province,city,district\n" +
//                    ") t4 \n" +
//                    "on t3.province = t4.province and t3.city = t4.city and t3.district = t4.district\n" +
//                    "left join \n" +
//                    "test_liu.\"address_mapping_district\" t5\n" +
//                    "on t3.province = t5.province and t3.city = t5.city and t3.district = t5.district\n" +
//                    ")\n");
            //修改后
            pst = conn.prepareStatement("insert into \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "select distinct platformid,province,city,district,firstcategoryid,secondcategoryid,thirdcategoryid,\n" +
                    "fourthcategoryid,sellcount,salesamount,flag_region_level,flag_cate_level,timestamp,sumPriceText,region_id,goodCount\n"+
                    " from \n"+
                    "("+
                            "select t3.platformid,t3.province,t3.city,t3.district,t3.firstcategoryid,t3.secondcategoryid,t3.thirdcategoryid,\n" +
                            "     t3.fourthcategoryid,t3.sellcount,t3.salesamount, '5' flag_region_level, '3' flag_cate_level,t3.timestamp,t3.sumPriceText,t3.goodCount,t4.uniqueid region_id\n"+
                            " from \n"+
                            "("+
                                    "select t1.good_id,t1.platformid,t1.pricetext, t1.province,t1.city,t1.district,t1.firstcategoryid,\n" +
                            "          t1.secondcategoryid,t1.thirdcategoryid,t1.fourthcategoryid,t1.sellcount,t1.salesamount, '5' flag_region_level, '3' flag_cate_level,t1.timestamp,\n" +
                            "          sum(pricetext) over(partition by t1.province,t1.city,t1.district) sumPriceText, \n" +
                            "          count(good_id) over(partition by t1.province,t1.city,t1.district) goodCount \n"+
                                    "from " + schem2 + ".\"" + name2 + "\" t1\n" +
                                    "left join dbadmin.\"O2O_stand_products_IDL\" t2\n" +
                            "          on t1.thirdcategoryid = t2.thirdcategoryid and t1.platformid = t2.platformid and t1.timestamp = t2.timestamp \n" +
                            "          where t2.product_type = '1'"+
                            ") t3 \n"+
                            "left join \n" +
                            "test_liu.\"address_mapping_district\" t4\n" +
                            "on t3.province = t4.province and t3.city = t4.city and t3.district = t4.district"+
                    ")\n");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            System.out.println("[Number of price data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入跨境表 苏宁
     */
    public static void o2o_screen_ovresea_app_suning_insert(Connection conn,
                                                            String schem1, String name1
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(\n" +
                    "province,\n" +
                    "city,\n" +
                    "district,\n" +
                    "originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "images,\n" +
                    "salesAmount,\n" +
                    "sellcount,\n" +
                    "fans, \n" +
                    "favcount,\n" +
                    "brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "start_time,\n" +
                    "end_time,\n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "brandname_en,\n" +
                    "regional_id,\n" +
                    "town,\n" +
                    "aedzid,\n" +
                    "starts,\n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopId\n" +
                    ")\n" +
                    "select \n" +
                    "province ,\n" +
                    "city,\n" +
                    "district,\n" +
                    "originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "images,\n" +
                    "salesAmount,\n" +
                    "cast(sellcount as float8),\n" +
                    "'' fans,       ----苏宁 \n" +
                    "'' favcount,\n" +
                    "brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "'' start_time, ----苏宁\n" +
                    "'' end_time,   ----苏宁\n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "brandname_en,\n" +
                    "reg_id regional_id,\n" +
                    "town,\n" +
                    "aedzid,\n" +
                    "'' starts,     ----苏宁\n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopId\n" +
                    "from \n" +
                    "g2019_suning \n" +
                    "where is_oversea = 'true'");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入跨境表 天猫
     */
    public static void o2o_screen_ovresea_app_tmall_insert(Connection conn,
                                                           String schem1, String name1
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(\n" +
                    "province,\n" +
                    "city,\n" +
                    "district,\n" +
                    "originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "images,\n" +
                    "salesAmount,\n" +
                    "sellcount,\n" +
                    "fans, \n" +
                    "favcount,\n" +
                    "brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "start_time,\n" +
                    "end_time,\n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "brandname_en,\n" +
                    "regional_id,\n" +
                    "town,\n" +
                    "aedzid,\n" +
                    "starts,\n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopId\n" +
                    ")\n" +
                    "select \n" +
                    "province ,\n" +
                    "city,\n" +
                    "district,\n" +
                    "originCountryName,\n" +
//                    "'' as originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "images,\n" +
                    "salesAmount,\n" +
                    "CAST (sellcount as float8) ,\n" +
                    "fans,      \n" +
                    "favcount,\n" +
                    "brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "'' start_time, \n" +
                    "'' end_time,  \n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "brandname_en,\n" +
                    "reg_id regional_id,\n" +
                    "'' as town,\n" +
                    "'' aedzid,\n" +
                    "starts,    \n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopId\n" +
                    "from \n" +
                    "g2019_tmall \n" +
                    "where \"is_oversea\" = 'true'");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入跨境表 淘宝
     */
    public static void o2o_screen_ovresea_app_taobao_insert(Connection conn,
                                                            String schem1, String name1, String timeStamp
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(\n" +
                    "province,\n" +
                    "city,\n" +
                    "district,\n" +
                    "originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "images,\n" +
                    "salesAmount,\n" +
                    "sellcount,\n" +
                    "fans, \n" +
                    "favcount,\n" +
                    "brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "start_time,\n" +
                    "end_time,\n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "brandname_en,\n" +
                    "regional_id,\n" +
                    "town,\n" +
                    "aedzid,\n" +
                    "starts,\n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopId\n" +
                    ")\n" +
                    "select \n" +
                    "province ,\n" +
                    "city,\n" +
                    "district,\n" +
//                    "originCountryName,\n" +
                    "'' as originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "images,\n" +
                    "cast((sellcount*priceText) as float8) as salesAmount,\n" +
                    "sellcount,\n" +
                    "fans,      \n" +
                    "favcount,\n" +
                    "brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "'' start_time, \n" +
                    "'' end_time,  \n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "brandname_en,\n" +
                    "regional_id,\n" +
                    "'' as town,\n" +
                    "'' as aedzid,\n" +
                    "starts,    \n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopId\n" +
                    "from \n" +
                    "\"test_liu\".\"g_2021_taobao_02_bu\"\n"+
                    "where \"is_oversea\" = 'true'");


            System.out.println("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(\n" +
                    "province,\n" +
                    "city,\n" +
                    "district,\n" +
                    "originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "images,\n" +
                    "salesAmount,\n" +
                    "sellcount,\n" +
                    "fans, \n" +
                    "favcount,\n" +
                    "brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "start_time,\n" +
                    "end_time,\n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "brandname_en,\n" +
                    "regional_id,\n" +
                    "town,\n" +
                    "aedzid,\n" +
                    "starts,\n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopId\n" +
                    ")\n" +
                    "select \n" +
                    "province ,\n" +
                    "city,\n" +
                    "district,\n" +
//                    "originCountryName,\n" +
                    "'' as originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "images,\n" +
                    "cast((sellcount*priceText) as float8) as salesAmount,\n" +
                    "sellcount,\n" +
                    "fans,      \n" +
                    "favcount,\n" +
                    "brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "'' start_time, \n" +
                    "'' end_time,  \n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "brandname_en,\n" +
                    "regional_id,\n" +
                    "'' as town,\n" +
                    "'' as aedzid,\n" +
                    "starts,    \n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopId\n" +
                    "from \n" +
                    "\"test_liu\".\"g_2021_taobao_02_bu\"\n"+
                    "where \"is_oversea\" = 'true'");


            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入跨境表 京东
     */
    public static void o2o_screen_ovresea_app_jd_insert(Connection conn,
                                                        String schem1, String name1
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(\n" +
                    "province,\n" +
                    "city,\n" +
                    "district,\n" +
                    "originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "images,\n" +
                    "salesAmount,\n" +
                    "sellcount,\n" +
                    "fans, \n" +
                    "favcount,\n" +
                    "brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "start_time,\n" +
                    "end_time,\n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "brandname_en,\n" +
                    "regional_id,\n" +
                    "town,\n" +
                    "aedzid,\n" +
                    "starts,\n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopId\n" +
                    ")\n" +
                    "select \n" +
                    "province ,\n" +
                    "city,\n" +
                    "district,\n" +
                    "originCountryName,\n" +
//                    "'' as originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "images,\n" +
                    "salesAmount,\n" +
                    "sellcount,\n" +
                    "fans,      \n" +
                    "favcount,\n" +
                    "brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "'' start_time, \n" +
                    "'' end_time,  \n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "brandname_en,\n" +
                    "reg_id regional_id,\n" +
                    "'' as town,\n" +
                    "'' aedzid,\n" +
                    "starts,    \n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopId\n" +
                    "from \n" +
                    "g2019_jd \n" +
                    "where \"is_oversea\" = 'true'");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 插入跨境表 拼多多
     */
    public static void o2o_screen_ovresea_app_pinduoduo_insert(Connection conn,
                                                               String schem1, String name1
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(\n" +
                    "province ,\n" +
                    "city,\n" +
                    "district,\n" +
                    "originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "images,\n" +
                    "salesAmount,\n" +
                    "sellcount,\n" +
                    "fans, \n" +
                    "favcount,\n" +
                    "brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "start_time,\n" +
                    "end_time,\n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "brandname_en,\n" +
                    "regional_id,\n" +
                    "town,\n" +
                    "aedzid,\n" +
                    "starts,\n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopId\n" +
                    ")\n" +
                    "select \n" +
                    "province ,\n" +
                    "city,\n" +
                    "district,\n" +
                    "originCountryName,\n" +
//                    "'' originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "'' images,\n" +
                    "salesAmount,\n" +
                    "sellcount,\n" +
                    "'' fans,       ----苏宁 \n" +
                    "'' favcount,\n" +
                    "'' brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "'' start_time, ----苏宁\n" +
                    "'' end_time,   ----苏宁\n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "'' brandname_en,\n" +
                    "reg_id regional_id,\n" +
                    "'' town,\n" +
                    "'' aedzid,\n" +
                    "'' starts,     ----苏宁\n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopId\n" +
                    "from \n" +
                    "g2019_pinduoduo \n" +
                    "where \"is_oversea\" = 'true'");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 创建小平台跨境表
     */
    public static void is_oversea_plat_create(Connection conn,
                                              String schem1, String name1,
                                              String timestamp,
                                              String plat
    ) {
        Statement stmt = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            stmt = conn.createStatement();
            int rc = 0;
            //执行普通SQL语句。
            if (plat.equals("kaola")) {
                rc = stmt.executeUpdate(
                        /**
                         *1.修改region_id
                         *
                         */
                        "create temporary table g2019_" + plat + " as\n" +
                                "select\n" +
                                "distinct\n" +
                                "t1.is_oversea,\n" +
                                "t1.province,\n" +
                                "t1.city,\n" +
                                "t1.district,\n" +
                                "t1.origincountryname,\n" +
                                "'' as originproduction,\n" +
                                "t1.good_id,\n" +
                                "t1.title,\n" +
                                "t1.goodurl,\n" +
                                "t1.images,\n" +
                                "t1.salesamount,\n" +
                                "t1.sellcount,\n" +
                                "'' as fans,\n" +
                                "'' as favcount,\n" +
                                "t1.brandname_cn,\n" +
                                "t1.name,\n" +
                                "t1.shopname,\n" +
                                "t1.shopurl,\n" +
                                "t1.platformid,\n" +
                                "'' as start_time,\n" +
                                "'' as end_time,\n" +
                                "t1.firstcategoryid,\n" +
                                "t1.secondcategoryid,\n" +
                                "t1.thirdcategoryid,\n" +
                                "t1.brandname_en,\n" +
                                "t1.regional_id,\n" +
                                "'' as town,\n" +
                                "'' as aedzid,\n" +
                                "'' as starts,\n" +
                                "t1.address,\n" +
                                "'' as evaluates,\n" +
                                "t1.timestamp,\n" +
                                "t1.shopid,\n" +
                                "'' as products,\n" +
                                "t2.uniqueid reg_id\n" +
                                "from\n" +
                                "(select \n" +
                                "*,\n" +
                                "case \n" +
                                "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n" +
                                "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟'  or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n" +
                                "else district end  districts\n" +
                                "from \"" + schem1 + "\".\"" + name1 + "\") t1\n" +
                                "left join\n" +
                                "(select province,city,district,uniqueid from  dbadmin.address_mapping_p_c_d_show_2018 where timestamp = '" + timestamp + "') t2\n" +
                                "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)"
                );
            } else if (plat.equals("secoo")) {
                rc = stmt.executeUpdate(
                        /**
                         *1.修改region_id
                         *
                         */
                        "create temporary table g2019_" + plat + " as\n" +
                                "select\n" +
                                "distinct\n" +
                                "t1.is_oversea,\n" +
                                "t1.province,\n" +
                                "t1.city,\n" +
                                "t1.district,\n" +
                                "t1.origincountryname,\n" +
                                "t1.originproduction,\n" +
                                "t1.good_id,\n" +
                                "t1.title,\n" +
                                "t1.goodurl,\n" +
                                "t1.images,\n" +
                                "t1.salesamount,\n" +
                                "t1.sellcount,\n" +
                                "'' as fans,\n" +
                                "'' as favcount,\n" +
                                "t1.brandname_cn,\n" +
                                "t1.name,\n" +
                                "t1.shopname,\n" +
                                "'' as shopurl,\n" +
                                "t1.platformid,\n" +
                                "'' as start_time,\n" +
                                "'' as end_time,\n" +
                                "t1.firstcategoryid,\n" +
                                "t1.secondcategoryid,\n" +
                                "t1.thirdcategoryid,\n" +
                                "t1.brandname_en,\n" +
                                "t1.regional_id,\n" +
                                "'' as town,\n" +
                                "'' as aedzid,\n" +
                                "'' as starts,\n" +
                                "t1.address,\n" +
                                "'' as evaluates,\n" +
                                "t1.timestamp,\n" +
                                "t1.shopid,\n" +
                                "'' as products,\n" +
                                "t2.uniqueid reg_id\n" +
                                "from\n" +
                                "(select \n" +
                                "*,\n" +
                                "case \n" +
                                "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n" +
                                "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟'  or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n" +
                                "else district end  districts\n" +
                                "from \"" + schem1 + "\".\"" + name1 + "\") t1\n" +
                                "left join\n" +
                                "(select province,city,district,uniqueid from  dbadmin.address_mapping_p_c_d_show_2018 where timestamp = '" + timestamp + "') t2\n" +
                                "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)"
                );
            } else if (plat.equals("beibei")) {
                rc = stmt.executeUpdate(
                        /**
                         *1.修改region_id
                         *
                         */
                        "create temporary table g2019_" + plat + " as\n" +
                                "select\n" +
                                "distinct\n" +
                                "t1.is_oversea,\n" +
                                "t1.province,\n" +
                                "t1.city,\n" +
                                "t1.district,\n" +
                                "t1.origincountryname,\n" +
                                "'' as originproduction,\n" +
                                "t1.good_id,\n" +
                                "t1.title,\n" +
                                "t1.goodurl,\n" +
                                "t1.images,\n" +
                                "t1.salesamount,\n" +
                                "t1.sellcount,\n" +
                                "t1.fans,\n" +
                                "'' as favcount,\n" +
                                "t1.brandname_cn,\n" +
                                "t1.name,\n" +
                                "t1.shopname,\n" +
                                "t1.shopurl,\n" +
                                "t1.platformid,\n" +
                                "'' as start_time,\n" +
                                "'' as end_time,\n" +
                                "t1.firstcategoryid,\n" +
                                "t1.secondcategoryid,\n" +
                                "t1.thirdcategoryid,\n" +
                                "t1.brandname_en,\n" +
                                "t1.regional_id,\n" +
                                "'' as town,\n" +
                                "'' as aedzid,\n" +
                                "'' as starts,\n" +
                                "t1.address,\n" +
                                "t1.evaluates,\n" +
                                "t1.timestamp,\n" +
                                "t1.shopid,\n" +
                                "'' as products,\n" +
                                "t2.uniqueid reg_id\n" +
                                "from\n" +
                                "(select \n" +
                                "*,\n" +
                                "case \n" +
                                "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n" +
                                "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n" +
                                "else district end  districts\n" +
                                "from \"" + schem1 + "\".\"" + name1 + "\") t1\n" +
                                "left join\n" +
                                "(select province,city,district,uniqueid from  dbadmin.address_mapping_p_c_d_show_2018 where timestamp = '" + timestamp + "') t2\n" +
                                "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)"
                );
            } else if (plat.equals("jumei")) {
                rc = stmt.executeUpdate(
                        /**
                         *1.修改region_id
                         *
                         */
                        "create temporary table g2019_" + plat + " as\n" +
                                "select\n" +
                                "distinct\n" +
                                "t1.is_oversea,\n" +
                                "t1.province,\n" +
                                "t1.city,\n" +
                                "t1.district,\n" +
                                "t1.origincountryname,\n" +
                                "'' as originproduction,\n" +
                                "t1.good_id,\n" +
                                "t1.title,\n" +
                                "t1.goodurl,\n" +
                                "t1.images,\n" +
                                "t1.salesamount,\n" +
                                "t1.sellcount,\n" +
                                "fans,\n" +
                                "t1.favcount,\n" +
                                "t1.brandname_cn,\n" +
                                "t1.name,\n" +
                                "t1.shopname,\n" +
                                "t1.shopurl,\n" +
                                "t1.platformid,\n" +
                                "'' as start_time,\n" +
                                "'' as end_time,\n" +
                                "t1.firstcategoryid,\n" +
                                "t1.secondcategoryid,\n" +
                                "t1.thirdcategoryid,\n" +
                                "t1.brandname_en,\n" +
                                "t1.regional_id,\n" +
                                "'' as town,\n" +
                                "'' as aedzid,\n" +
                                "'' as starts,\n" +
                                "t1.address,\n" +
                                "'' as evaluates,\n" +
                                "t1.timestamp,\n" +
                                "t1.shopid,\n" +
                                "'' as products,\n" +
                                "t2.uniqueid reg_id\n" +
                                "from\n" +
                                "(select \n" +
                                "*,\n" +
                                "case \n" +
                                "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n" +
                                "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n" +
                                "else district end  districts\n" +
                                "from \"" + schem1 + "\".\"" + name1 + "\") t1\n" +
                                "left join\n" +
                                "(select province,city,district,uniqueid from  dbadmin.address_mapping_p_c_d_show_2018 where timestamp = '" + timestamp + "') t2\n" +
                                "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)"
                );
            } else if (plat.equals("shunfeng")) {
                rc = stmt.executeUpdate(
                        /**
                         *1.修改region_id
                         *
                         */
                        "create temporary table g2019_" + plat + " as\n" +
                                "select\n" +
                                "distinct\n" +
                                "t1.is_oversea,\n" +
                                "t1.province,\n" +
                                "t1.city,\n" +
                                "t1.district,\n" +
                                "t1.origincountryname,\n" +
                                "t1.originproduction,\n" +
                                "t1.good_id,\n" +
                                "t1.title,\n" +
                                "t1.goodurl,\n" +
                                "t1.images,\n" +
                                "t1.salesamount,\n" +
                                "t1.sellcount,\n" +
                                "'' as fans,\n" +
                                "'' as favcount,\n" +
                                "t1.brandname_cn,\n" +
                                "t1.name,\n" +
                                "t1.shopname,\n" +
                                "'' as shopurl,\n" +
                                "t1.platformid,\n" +
                                "'' as start_time,\n" +
                                "'' as end_time,\n" +
                                "t1.firstcategoryid,\n" +
                                "t1.secondcategoryid,\n" +
                                "t1.thirdcategoryid,\n" +
                                "t1.brandname_en,\n" +
                                "t1.regional_id,\n" +
                                "'' as town,\n" +
                                "'' as aedzid,\n" +
                                "'' as starts,\n" +
                                "t1.address,\n" +
                                "'' as evaluates,\n" +
                                "t1.timestamp,\n" +
                                "'' as shopid,\n" +
                                "'' as products,\n" +
                                "t2.uniqueid reg_id\n" +
                                "from\n" +
                                "(select \n" +
                                "*,\n" +
                                "case \n" +
                                "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n" +
                                "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n" +
                                "else district end  districts\n" +
                                "from \"" + schem1 + "\".\"" + name1 + "\") t1\n" +
                                "left join\n" +
                                "(select province,city,district,uniqueid from  dbadmin.address_mapping_p_c_d_show_2018 where timestamp = '" + timestamp + "') t2\n" +
                                "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)"
                );
            } else if (plat.equals("miya")) {
                rc = stmt.executeUpdate(
                        /**
                         *1.修改region_id
                         *
                         */
                        "create temporary table g2019_" + plat + " as\n" +
                                "select\n" +
                                "distinct\n" +
                                "t1.is_oversea,\n" +
                                "t1.province,\n" +
                                "t1.city,\n" +
                                "t1.district,\n" +
                                "t1.origincountryname,\n" +
                                "t1.originproduction,\n" +
                                "t1.good_id,\n" +
                                "t1.title,\n" +
                                "t1.goodurl,\n" +
                                "t1.images,\n" +
                                "t1.salesamount,\n" +
                                "t1.sellcount,\n" +
                                "'' as fans,\n" +
                                "''as favcount,\n" +
                                "t1.brandname_cn,\n" +
                                "t1.name,\n" +
                                "t1.shopname,\n" +
                                "'' as shopurl,\n" +
                                "t1.platformid,\n" +
                                "'' as start_time,\n" +
                                "'' as end_time,\n" +
                                "t1.firstcategoryid,\n" +
                                "t1.secondcategoryid,\n" +
                                "t1.thirdcategoryid,\n" +
                                "t1.brandname_en,\n" +
                                "t1.regional_id,\n" +
                                "'' as town,\n" +
                                "'' as aedzid,\n" +
                                "'' as starts,\n" +
                                "t1.address,\n" +
                                "'' as evaluates,\n" +
                                "t1.timestamp,\n" +
                                "t1.shopid,\n" +
//                                "'' as products,\n" +
                                "t1.products,\n" +
                                "t2.uniqueid reg_id\n" +
                                "from\n" +
                                "(select \n" +
                                "*,\n" +
                                "case \n" +
                                "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n" +
                                "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n" +
                                "else district end  districts\n" +
                                "from \"" + schem1 + "\".\"" + name1 + "\") t1\n" +
                                "left join\n" +
                                "(select province,city,district,uniqueid from  dbadmin.address_mapping_p_c_d_show_2018 where timestamp = '" + timestamp + "') t2\n" +
                                "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)\n"
                );
            } else if (plat.equals("yunji")) {
                rc = stmt.executeUpdate(
                        /**
                         *1.修改region_id
                         *
                         */
                        "create temporary table g2019_" + plat + " as\n" +
                                "select\n" +
                                "distinct\n" +
                                "t1.is_oversea,\n" +
                                "t1.province,\n" +
                                "t1.city,\n" +
                                "t1.district,\n" +
                                "t1.origincountryname,\n" +
                                "'' as originproduction,\n" +
                                "t1.good_id,\n" +
                                "t1.title,\n" +
                                "t1.goodurl,\n" +
                                "t1.images,\n" +
                                "t1.salesamount,\n" +
                                "t1.sellcount,\n" +
//                                "'' as fans,\n" +
                                "t1.fans,\n" +
                                "'' as favcount,\n" +
                                "t1.brandname_cn,\n" +
                                "t1.name,\n" +
                                "t1.shopname,\n" +
//                                "'' as shopurl,\n" +
                                "t1.shopurl,\n" +
                                "t1.platformid,\n" +
                                "'' as start_time,\n" +
                                "'' as end_time,\n" +
                                "t1.firstcategoryid,\n" +
                                "t1.secondcategoryid,\n" +
                                "t1.thirdcategoryid,\n" +
                                "t1.brandname_en,\n" +
                                "t1.regional_id,\n" +
                                "'' as town,\n" +
                                "'' as aedzid,\n" +
                                "'' as starts,\n" +
                                "t1.address,\n" +
                                "'' as evaluates,\n" +
                                "t1.timestamp,\n" +
                                "t1.shopid,\n" +
                                "'' as products,\n" +
                                "t2.uniqueid reg_id\n" +
                                "from\n" +
                                "(select \n" +
                                "*,\n" +
                                "case \n" +
                                "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n" +
                                "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟'  or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n" +
                                "else district end  districts\n" +
                                "from \"" + schem1 + "\".\"" + name1 + "\") t1\n" +
                                "left join\n" +
                                "(select province,city,district,uniqueid from  dbadmin.address_mapping_p_c_d_show_2018 where timestamp = '" + timestamp + "') t2\n" +
                                "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)"
                );
            } else if (plat.equals("babytree")) {
                rc = stmt.executeUpdate(
                        /**
                         *1.修改region_id
                         *
                         */
                        "create temporary table g2019_" + plat + " as\n" +
                                "select\n" +
                                "distinct\n" +
                                "t1.is_oversea,\n" +
                                "t1.province,\n" +
                                "t1.city,\n" +
                                "t1.district,\n" +
                                "'' as origincountryname,\n" +
                                "'' as originproduction,\n" +
                                "t1.good_id,\n" +
                                "t1.title,\n" +
                                "t1.goodurl,\n" +
                                "t1.images,\n" +
                                "t1.salesamount,\n" +
                                "t1.sellcount,\n" +
                                "'' as fans,\n" +
                                "'' as favcount,\n" +
                                "t1.brandname_cn,\n" +
                                "t1.name,\n" +
                                "t1.shopname,\n" +
                                "t1.shopurl,\n" +
                                "t1.platformid,\n" +
                                "'' as start_time,\n" +
                                "'' as end_time,\n" +
                                "t1.firstcategoryid,\n" +
                                "t1.secondcategoryid,\n" +
                                "t1.thirdcategoryid,\n" +
                                "t1.brandname_en,\n" +
                                "t1.regional_id,\n" +
                                "'' as town,\n" +
                                "'' as aedzid,\n" +
                                "'' as starts,\n" +
                                "t1.address,\n" +
                                "'' as evaluates,\n" +
                                "t1.timestamp,\n" +
                                "t1.shopid,\n" +
                                "'' as products,\n" +
                                "t2.uniqueid reg_id\n" +
                                "from\n" +
                                "(select \n" +
                                "*,\n" +
                                "case \n" +
                                "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n" +
                                "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟'  or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n" +
                                "else district end  districts\n" +
                                "from \"" + schem1 + "\".\"" + name1 + "\") t1\n" +
                                "left join\n" +
                                "(select province,city,district,uniqueid from  dbadmin.address_mapping_p_c_d_show_2018 where timestamp = '" + timestamp + "') t2\n" +
                                "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)"
                );
            } else if (plat.equals("dangdang")) {
                rc = stmt.executeUpdate(
                        /**
                         *1.修改region_id
                         *
                         */
                        "create temporary table g2019_" + plat + " as\n" +
                                "select\n" +
                                "distinct\n" +
                                "t1.is_oversea,\n" +
                                "t1.province,\n" +
                                "t1.city,\n" +
                                "t1.district,\n" +
                                "t1.origincountryname,\n" +
                                "'' as originproduction,\n" +
                                "t1.good_id,\n" +
                                "t1.title,\n" +
                                "t1.goodurl,\n" +
                                "t1.images,\n" +
                                "t1.salesamount,\n" +
                                "t1.sellcount,\n" +
                                "'' as fans,\n" +
                                "t1.favcount,\n" +
                                "t1.brandname_cn,\n" +
                                "t1.name,\n" +
                                "t1.shopname,\n" +
                                "t1.shopurl,\n" +
                                "t1.platformid,\n" +
                                "'' as start_time,\n" +
                                "'' as end_time,\n" +
                                "t1.firstcategoryid,\n" +
                                "t1.secondcategoryid,\n" +
                                "t1.thirdcategoryid,\n" +
                                "t1.brandname_en,\n" +
                                "t1.regional_id,\n" +
                                "'' as town,\n" +
                                "'' as aedzid,\n" +
                                "'' as starts,\n" +
                                "t1.address,\n" +
                                "t1.evaluates,\n" +
                                "t1.timestamp,\n" +
                                "t1.shopid,\n" +
                                "'' as products,\n" +
                                "t2.uniqueid reg_id\n" +
                                "from\n" +
                                "(select \n" +
                                "*,\n" +
                                "case \n" +
                                "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n" +
                                "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟'   or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n" +
                                "else district end  districts\n" +
                                "from \"" + schem1 + "\".\"" + name1 + "\") t1 \n" +
                                "left join\n" +
                                "(select province,city,district,uniqueid from  dbadmin.address_mapping_p_c_d_show_2018 where timestamp = '" + timestamp + "') t2\n" +
                                "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)"
                );
            } else if (plat.equals("guomei")) {
                rc = stmt.executeUpdate(
                        /**
                         *1.修改region_id
                         *
                         */
                        "create temporary table g2019_" + plat + " as\n" +
                                "select\n" +
                                "distinct\n" +
                                "t1.is_oversea,\n" +
                                "t1.province,\n" +
                                "t1.city,\n" +
                                "t1.district,\n" +
                                "t1.origincountryname,\n" +
                                "'' as originproduction,\n" +
                                "t1.good_id,\n" +
                                "t1.title,\n" +
                                "t1.goodurl,\n" +
                                "t1.images,\n" +
                                "t1.salesamount,\n" +
                                "t1.sellcount,\n" +
                                "'' as fans,\n" +
                                "'' as favcount,\n" +
                                "t1.brandname_cn,\n" +
                                "t1.name,\n" +
                                "t1.shopname,\n" +
                                "t1.shopurl,\n" +
                                "t1.platformid,\n" +
                                "'' as start_time,\n" +
                                "'' as end_time,\n" +
                                "t1.firstcategoryid,\n" +
                                "t1.secondcategoryid,\n" +
                                "t1.thirdcategoryid,\n" +
                                "t1.brandname_en,\n" +
                                "t1.regional_id,\n" +
                                "'' as town,\n" +
                                "'' as aedzid,\n" +
                                "'' as starts,\n" +
                                "t1.address,\n" +
                                "'' as evaluates,\n" +
                                "t1.timestamp,\n" +
                                "t1.shopid,\n" +
                                "'' as products,\n" +
                                "t2.uniqueid reg_id\n" +
                                "from\n" +
                                "(select \n" +
                                "*,\n" +
                                "case \n" +
                                "when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n" +
                                "when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n" +
                                "else district end  districts\n" +
                                "from \"" + schem1 + "\".\"" + name1 + "\") t1\n" +
                                "left join\n" +
                                "(select province,city,district,uniqueid from  dbadmin.address_mapping_p_c_d_show_2018 where timestamp = '" + timestamp + "') t2\n" +
                                "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)"
                );
            }

            stmt.close();
        } catch (SQLException e) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 插入小平台跨境表
     */
    public static void is_oversea_plat_insert(Connection conn,
                                              String schem1, String name1,
                                              String plat
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(\n" +
                    "province ,\n" +
                    "city,\n" +
                    "district,\n" +
                    "originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "images,\n" +
                    "salesAmount,\n" +
                    "sellcount,\n" +
                    "fans, \n" +
                    "favcount,\n" +
                    "brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "start_time,\n" +
                    "end_time,\n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "brandname_en,\n" +
                    "regional_id,\n" +
                    "town,\n" +
                    "aedzid,\n" +
                    "starts,\n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopid\n" +
                    ")\n" +
                    "select \n" +
                    "province ,\n" +
                    "city,\n" +
                    "district,\n" +
                    "originCountryName,\n" +
                    "originProduction,\n" +
                    "good_id,\n" +
                    "title,\n" +
                    "goodurl,\n" +
                    "images,\n" +
                    "salesAmount,\n" +
                    "sellcount,\n" +
                    "fans,       \n" +
                    "favcount,\n" +
                    "brandname_cn,\n" +
                    "name,\n" +
                    "shopname,\n" +
                    "shopurl,\n" +
                    "platformid,\n" +
                    "timestamp,\n" +
                    "'' start_time,\n" +
                    "'' end_time,   \n" +
                    "firstcategoryid,\n" +
                    "secondcategoryid,\n" +
                    "thirdcategoryid,\n" +
                    "brandname_en,\n" +
                    "reg_id regional_id,\n" +
                    "town,\n" +
                    "aedzid,\n" +
                    "starts,     \n" +
                    "address,\n" +
                    "evaluates,\n" +
                    "shopid\n" +
                    "from \n" +
                    "g2019_" + plat + " \n" +
                    "where is_oversea = 'true'");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    public static void bestsell_plat_taobao(Connection conn,
                                            String schem1, String name1,
                                            String schem2, String name2
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(good_id,sellCount,salesAmount,platformid,timeStamp,region_id,shopid\n" +
                    ",shopname,shopurl,title,goodurl,name,province,city,district)\n" +
                    "select distinct\n" +
                    "t1.good_id,t1.sellcount,t1.salesamount,t1.platformId\n" +
                    ",t1.timestamp,t1.regional_id,t1.shopid,t1.shopname,t1.shopurl\n" +
                    ",t1.title,t1.goodurl,'' as name,t1.province,t1.city,t1.district\n" +
                    "from\n" +
                    "(\n" +
                    "select *,row_number() over(partition by regional_id,province,city\n" +
                    ",district order by cast(salesamount as float8) desc) rank\n" +
                    "from\n" +
                    "\"" + schem2 + "\".\"" + name2 + "\"\n" +
                    "where \n" +
                    "is_oversea != 'true' and \n" +
                    "province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛')\n" +
                    " ) t1\n" +
                    "where t1.rank <= 150\n");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }


    }


    public static void bestsell_plat_tmalljdsuning(Connection conn,
                                                   String schem1, String name1,
                                                   String schem2, String name2
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(good_id,sellCount,salesAmount,platformid,timeStamp,region_id,shopid\n" +
                    ",shopname,shopurl,title,goodurl,name,province,city,district)\n" +
                    "select\n" +
                    "t1.good_id,cast(t1.sellcount as INTEGER),t1.salesamount,t1.platformId\n" +
                    ",t1.timestamp,t1.regional_id,t1.shopid,t1.shopname,t1.shopurl\n" +
                    ",t1.title,t1.goodurl,t1.name,t1.province,t1.city,t1.district\n" +
                    "from\n" +
                    "(\n" +
                    "select good_id,cast(sellcount as float8),salesamount,platformId,timestamp,regional_id,shopid,shopname,shopurl,title,goodurl,name,province,city,district," +
                    "row_number() over(partition by regional_id,province,city\n" +
                    ",district order by cast(salesamount as float8) desc) rank\n" +
                    "from\n" +
                    "\"" + schem2 + "\".\"" + name2 + "\"\n" +
                    "where \n" +
                    "is_oversea != 'true' and \n" +
                    "province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛')\n" +
                    " ) t1\n" +
                    "where t1.rank <= 150\n");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }


    }

    public static void bestsell_plat_meituan(Connection conn,
                                             String schem1, String name1,
                                             String schem2, String name2
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(good_id,sellCount,salesAmount,platformid,timeStamp,region_id,shopid\n" +
                    ",shopname,shopurl,title,goodurl,name,province,city,district)\n" +
                    "select distinct\n" +
                    "t1.good_id,CAST(t1.sellcount as float8),t1.salesamount,t1.platformId\n" +
                    ",t1.timestamp,t1.regional_id,t1.shopid,t1.shopname,t1.shopurl\n" +
                    ",t1.title,'' as goodurl,'' as name,t1.province,t1.city,t1.district\n" +
                    "from\n" +
                    "(\n" +
                    "select *,row_number() over(partition by regional_id,province,city\n" +
                    ",district order by cast(salesamount as float8) desc) rank\n" +
                    "from\n" +
                    "\"" + schem2 + "\".\"" + name2 + "\"\n" +
                    "where \n" +
                    "--is_oversea != 'true' and \n" +
                    "province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛') --and length(shopid)='32'\n" +
                    " ) t1\n" +
                    "where t1.rank <= 150\n");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }


    }

    public static void bestsell_plat_pinduoduo(Connection conn,
                                               String schem1, String name1,
                                               String schem2, String name2
    ) {
        PreparedStatement pst = null;
        try {

            pst = conn.prepareStatement("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n" +
                    "(good_id,sellCount,salesAmount,platformid,timeStamp,region_id,shopid\n" +
                    ",shopname,shopurl,title,goodurl,name,province,city,district)\n" +
                    "select\n" +
                    "t1.good_id,t1.sellcount,t1.salesamount,t1.platformId\n" +
//                    ",t1.timestamp,t1.regional_id,t1.shopid,'' as shopname,'' as shopurl\n" +
                    ",t1.timestamp,t1.regional_id,t1.shopid,t1.shopname,'' as shopurl\n" +
                    ",t1.title,'' as goodurl,'' as name,t1.province,t1.city,t1.district\n" +
                    "from\n" +
                    "(\n" +
                    "select *,row_number() over(partition by regional_id,province,city\n" +
                    ",district order by cast(salesamount as float8) desc) rank\n" +
                    "from\n" +
                    "\"" + schem2 + "\".\"" + name2 + "\"\n" +
                    "where \n" +
                    "--is_oversea != 'true' and \n" +
                    "province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛')\n" +
                    " ) t1\n" +
                    "where t1.rank <= 150\n");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }


    }

    /**
     * 更新跨境表
     */
    public static void is_oversea_plat_update(Connection conn,
                                              String schem1, String name1
    ) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(
                    "update \"" + schem1 + "\".\"" + name1 + "\"\n" +
                            "set \n" +
                            "\"regional_id\" = \n" +
                            "case \n" +
                            "when province = '北京市' and city = '市辖区' and district  ='东城区' then '110101'\n" +
                            "when province = '山东省' and city = '莱芜市' and district  ='0' then '3701'\n" +
                            "when province = '山东省' and city = '莱芜市' and district  ='莱城区' then '371202'\n" +
                            "when province = '北京市' and city = '朝阳区' and district  ='朝阳区' then '110105'\n" +
                            "when province = '北京市' and city = '海淀区' and district  ='海淀区' then '110108'\n" +
                            "when province = '山东省' and city = '莱芜市' and district  ='钢城区' then '371203'\n" +
                            "when province = '北京市' and city = '市辖区' and district  ='顺义区' then '110113'\n" +
                            "when province = '广东省' and city = '中山市' and district  ='中山市' then '4420'\n" +
                            "when province = '北京市' and city = '市辖区' and district  ='0' then '1101'\n" +
                            "when province = '北京市' and city = '大兴区' and district  ='大兴区' then '110115'\n" +
                            "when province = '广东省' and city = '东莞市' and district  ='东莞市' then '4419'\n" +
                            "when province = '湖北省' and city = '武汉市' and district  ='蔡甸区' then '420114'\n" +
                            "when province = '上海市' and city = '市辖区' and district  ='0' then '3101'\n" +
                            "when province = '吉林省' and city = '长春市' and district  ='0' then '2201'\n" +
                            "when province = '山东省' and city = '济南市' and district  ='0' then '3701'\n" +
                            "when province = '山东省' and city = '临沂市' and district  ='0' then '3713'\n" +
                            "when province = '江西省' and city = '南昌市' and district  ='0' then '3601'\n" +
                            "when province = '北京市' and city = '市辖区' and district  ='朝阳区' then '110105'\n" +
                            "when province = '山东省' and\tcity = '烟台市'\t   and district= '0' then '3706'\n" +
                            "when province = '广东省' and\tcity = '佛山市'\t   and district= '0' then '4406'\n" +
                            "when province = '上海市' and\tcity = '浦东新区'  and district= '浦东新区' then '310115'\n" +
                            "when province = '广东省' and\tcity = '东莞市'\t   and district= '0' then '4419'\n" +
                            "when province = '福建省' and\tcity = '福州市'\t   and district= '0' then '3501'\n" +
                            "when province = '广东省' and\tcity = '中山市'\t   and district= '0' then '4420'\n" +
                            "when province = '山东省' and\tcity = '济宁市'\t   and district= '0' then '3708'\n" +
                            "when province = '山西省' and\tcity = '太原市'\t   and district= '0' then '1401'\n" +
                            "when province = '北京市' and\tcity = '北京市'\t   and district= '0' then '1101'\n" +
                            "when province = '浙江省' and\tcity = '温州市'\t   and district= '0' then '3303'\n" +
                            "when province = '辽宁省' and\tcity = '沈阳市'\t   and district= '0' then '2101'\n" +
                            "when province = '四川省' and\tcity = '自贡市'\t   and district= '0' then '5103'\n" +
                            "when province = '广东省' and\tcity = '深圳市'\t   and district= '0' then '4403'\n" +
                            "when province = '浙江省' and\tcity = '宁波市'\t   and district= '0' then '3302'\n" +
                            "when province = '天津市' and\tcity = '天津市'\t   and district= '0' then '1201'\n" +
                            "when province = '上海市' and\tcity = '金山区'\t   and district= '金山区' then '310116'\n" +
                            "when province = '上海市' and\tcity = '上海市'\t   and district= '0' then '3101'\n" +
                            "when province = '江苏省' and\tcity = '宿迁市'\t   and district= '沭阳县' then '321322'\n" +
                            "when province = '安徽省' and\tcity = '合肥市'\t   and district= '0' then '3401'\n" +
                            "when province = '河南省' and\tcity = '开封市'\t   and district= '0' then '4102'\n" +
                            "when province = '浙江省' and\tcity = '杭州市'\t   and district= '0' then '3301'\n" +
                            "when province = '山东省' and\tcity = '青岛市'\t   and district= '0' then '3702'\n" +
                            "when province = '北京市' and\tcity = '北京市'\t   and district= '朝阳区' then '110105'\n" +
                            "when province = '江苏省' and\tcity = '苏州市'\t   and district= '姑苏区' then '320508'\n" +
                            "when province = '湖北省' and\tcity = '武汉市'\t   and district= '0' then '4201'\n" +
                            "when province = '四川省' and\tcity = '成都市'\t   and district= '0' then '5101'\n" +
                            "when province = '海外' and\tcity isnull\t   and district isnull then '99'\n" +
                            "when province = '台湾省' and\tcity = '0'\t   and district= '0' then '71'\n" +
                            "when province = '香港特别行政区' and\tcity isnull\t   and district isnull then '81'\n" +
                            "when province = '台湾省' and city isnull\t   and district isnull then '71'\n" +
                            "when province = '澳门特别行政区' and\tcity isnull\t   and district isnull then '82'\n" +
                            "when province = '0' and\tcity = '0'\t   and district= '0' then '-1'\n" +
                            "else \"regional_id\" end \n" +
                            "where regional_id is null");
            int i = pstmt.executeUpdate();
            logger.info("[Number of data update:" + i + "]");
            pstmt.close();
        } catch (SQLException e) {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


}
